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

"Rob Richardson" <Rob.Richardson@rad-con.com> writes:
> Our database monitors the progression of steel coils through the
> annealing process.  The times for each step are recorded in wallclock
> time (US eastern time zone for this customer) and in UTC time.  During
> standard time, the difference will be 5 hours, and during daylight
> savings time the difference will be 4 hours.

It seems to me that you're not entirely understanding how timestamps
work in Postgres.  The above is quite unnecessary, and the way that
you're computing the data to store looks wrong too.  I think the
problem is that you are inserting unnecessary (and incorrect)
conversions because of sloppiness about data types.

You started with (to simplify matters) 9:39pm last Saturday:

# select '2010-03-13 21:39 EST'::timestamptz;
      timestamptz
------------------------
 2010-03-13 21:39:00-05
(1 row)

Now what this is under the hood is a *UTC time*.  The fact that I
entered it as a time with respect to EST zone doesn't change that;
it got rotated to UTC internally.  The display as EST doesn't change
it either; that's because the internal value is rotated back to my
TimeZone setting (EST5EDT) for display.  So the actual internal
value is equivalent to 2010-03-14 02:39:00 UTC.  (In your problem
case, that was what you got from current_timestamp, but we can
experiment with this manually entered value instead.)

You then did this:

>     select into UTCTimestamp current_timestamp at time zone 'UTC';

What the AT TIME ZONE expression produces is a timestamp WITHOUT time
zone value, which will be '2010-03-14 02:39:00' without reference to
any particular time zone:

# select '2010-03-13 21:39 EST'::timestamptz at time zone 'UTC';
      timezone
---------------------
 2010-03-14 02:39:00
(1 row)

Now at this point I have to guess, since you didn't show us the declared
data types of any of the variables involved, but I'm going to guess that
the local variable UTCTimestamp is declared as timestamp WITH time zone
(timestamptz) whereas the fire_date and fire_date_utc columns are
timestamp WITHOUT time zone.  Since the result of the AT TIME ZONE
construct is timestamp WITHOUT time zone, it will have to be converted
to timestamp WITH time zone to be stored into UTCTimestamp.  And since
the value has no attached time zone, the conversion process will assume
that it's relative to the zone specified by TimeZone.  So that means
it's interpreted as 2010-03-14 02:39:00 in EST5EDT.  And there's a bit
of a problem with that: since we jumped from 02:00 to 03:00 local time,
there *was* no instant when a properly functioning clock would have read
02:39 local time.  You could make an argument for throwing an error
here, but what the timestamp input routine actually does is to assume
that local standard time was meant.  So the result is the equivalent
of 07:39 UTC (five-hour offset from the given time).  If I do this by
hand I get

# select '2010-03-14 02:39:00'::timestamptz;
      timestamptz
------------------------
 2010-03-14 03:39:00-04
(1 row)

The display is 03:39 EDT, which is what an east-coast clock would
actually have read at 07:39 UTC.  Remember that the internal value
is just UTC; the rotation to 03:39 is an I/O or conversion behavior.

And then lastly you stored this value into a timestamp WITHOUT time zone
column.  That means it gets rotated to the TimeZone zone, as if for
display.  So what went into the fire_date_utc column is '2010-03-14
03:39:00', sans any identifying information that would have clarified
what this was supposed to mean.

Meanwhile, your fire_date column was set directly from current_timestamp
without any intermediate shenanigans, so what it got was 02:39 UTC
rotated just once to local time, producing 21:39 of the previous day as
expected.

If my guesses are correct, then the minimum change to avoid this type
of problem in the future is to change UTCTimestamp to be declared as
timestamp WITHOUT time zone, so that you don't get two extra zone
rotations in there.  However, I would strongly suggest that you rethink
how you're storing the data altogether.  Two columns that represent the
identical item of information is not good database design according to
any theory I've ever heard.  What I'd store is a single fire_date column
that is of type timestamp with time zone and is just assigned directly
from current_timestamp without any funny business.  Internally it is UTC
and completely unambiguous.  Subsequently you can read it out in any
time zone you want, either by setting TimeZone appropriately or by using
the AT TIME ZONE construct to do a one-time conversion.

            regards, tom lane