Re: Daylight savings time confusion - Mailing list pgsql-general

Rob Richardson wrote:
> Greetings!
>
> ...
> I just looked at the record for a charge for which heating started just
> after 9:00 Saturday night, less than 3 hours before the change to
> daylight savings time.  The UTC time stored for this event is six hours
> later!
>
> The function that writes these times first stores the UTC time in a
> variable named UTCTimestamp:
>
>     select into UTCTimestamp current_timestamp at time zone 'UTC';
>
> Then, later in the function, the two times get written into the record
> (along with some other stuff):
>
>             update charge set
> status=ChargeStatus,fire_date=current_timestamp,
> fire_date_utc=UTCTimestamp, fire_user=FurnaceTender,
> updated_by=UserId,updated_date=current_timestamp where charge=ChargeNum;
>
> Can someone explain why fire_date is 2010-03-13 21:39:51.744 and
> fire_date_utc is 2010-03-14 03:39:51.744 for this record?
>
> There is another charge that began firing five and a half hours before
> the DST switch.  The difference between its fire_date and fire_date_utc
> times is five hours, as expected....
>


I think you are shooting yourself in the foot with the different
timestamp columns. Time is time is time and you only need one column to
represent it. I think the problems were masked until the time-zone
change. (Trust me, I'm having my own fun, today. Try "date -d yesterday"
between midnight and 1am the day after springing forward and you get the
11pm hour Saturday but "date -d '0015 2010-03-15 -1 day' gives fifteen
minutes past midnight on the 14th.)

It is a bit difficult to trace everything without seeing your full
functions and column types but I believe that the first issue is that
when you specify the timezone, the result does not include the time-zone
offset (timestamp without tz). Note that there is no -00 (or +00) and
there isn't one regardless of zone:

select now(),now() at time zone 'UTC' as utc, now() at time zone
'America/New_York' as ny;
-[ RECORD 1 ]-------------------------
now | 2010-03-15 15:34:52.3342-07
utc | 2010-03-15 22:34:52.3342
ny  | 2010-03-15 18:34:52.3342

Now see what happens if you run:
select current_timestamp, (select current_timestamp at time zone
'UTC')::timestamptz ;
-[ RECORD 1 ]------------------------------
now         | 2010-03-15 15:39:44.594979-07
timestamptz | 2010-03-15 22:39:44.594979-07

Two timestamptz columns offset by 7 hours. (Really offset - they are
both displayed in Pacific Daylight Time).

The second issue is that depending on which of your columns/variables
are with or without the zone information and how you do your
calculations, you could easily end up with a situation where your
current time is Standard so your program "knows" the correct offset to
be 5 hours which you add to a 9pm timestamptz. Given the missing hour,
9pm plus 5 hours gets you to 3am. But if you are mix-and-matching
timestamps with and without time-zone you are in for some interesting
problems.

Finally, beware that time handling has been updated across PG versions.
For example, "select now() - '1 day'::interval" works differently in,
7.4 (if run early Monday after a time change you will end up with late
Saturday) than in 8.4 (you get the current time of day on Sunday). So if
you take the difference between those two timestamps in 7.4 it is 24
hours but in 8.4 it is 23 hours.

A better approach is to store the fully-qualified timestamp in a single
column of type timestamptz instead of duplicated columns that are
supposed to represent the same point in time (but different zones). Then
display that one column in whatever timezone(s) you want:
select
    now() as local,
    now() at time zone 'America/New_York' as eastern,
    now() at time zone 'CST6CDT' as central,
    now() at time zone 'Chile/Continental' as chile,
    now() at time zone 'Africa/Addis_Ababa' as ethiopia;
-[ RECORD 1 ]---------------------------
local    | 2010-03-15 15:47:01.644575-07
eastern  | 2010-03-15 18:47:01.644575
central  | 2010-03-15 17:47:01.644575
chile    | 2010-03-15 18:47:01.644575
ethiopia | 2010-03-16 01:47:01.644575

Cheers,
Steve