PostgreSQL wrong converting from timestamp without time zone to timestamp with time zone

Question

I faced with the following issue this morning:

select '2011-12-30 00:30:00'::timestamp without time zone AT TIME ZONE 'EST5EDT';

returns me 2011-12-30 05:30:00+00 witch is wrong.

But next queries below:

select '2011-12-30 00:30:00'::timestamp without time zone AT TIME ZONE 'UTC-5';
select '2011-12-30 00:30:00' AT TIME ZONE 'EST5EDT';

i see right date 2011-12-29 19:30:00

Preventing your question about my local timezone:

SELECT  current_setting('TIMEZONE');
current_setting
-----------------
     UTC
(1 row)

Do anyone have answer why postgresql converts timestamp without time zone some weird way and instead taking away 5 hours it adds instead?

Venterea

Answer 1

Key things to understand

timestamp without time zone AT TIME ZONE re-interprets a timestamp as being in that time zone for the purpose of converting it to UTC.

timestamp with time zone AT TIME ZONE converts a timestamptz into a timestamp at the specified timezone.

PostgreSQL uses ISO-8601 timezones, which specify that east of Greenwich is positive ... unless you use a POSIX timezone specifier, in which case it follows POSIX. Insanity ensues.

Why the first one produces an unexpected result

Timestamps and timezones in SQL are horrible. This:

select '2011-12-30 00:30:00'::timestamp without time zone AT TIME ZONE 'EST5EDT';

inteprets the unknown-typed literal '2011-12-30 00:30:00' as timestamp without time zone, which Pg assumes is in the local TimeZone unless told otherwise. When you use AT TIME ZONE, it is (per the spec) re-interpreted as a timestamp with time zone in the time zone EST5EDT then stored as an absolute time in UTC - so it's converted from EST5EDT to UTC, i.e the timezone offset gets subtracted. x - (-5) is x + 5.

This timestamp, adjusted to UTC storage, is then adjusted for your server TimeZone setting for display so that it gets displayed in local time.

If you instead wish to say "I have this timestamp in UTC time, and wish to see what the equivalent local time in EST5EDT is", if you want to be independent of the server TimeZone setting, you need to write something like:

select TIMESTAMP '2011-12-30 00:30:00' AT TIME ZONE 'UTC'
       AT TIME ZONE 'EST5EDT';

This says "Given timestamp 2011-12-30 00:30:00, treat it as a timestamp in UTC when converting to timestamptz, then convert that timestamptz to a local time in EST5EDT".

Horrible, isn't it? I want to give a firm talking to whoever decided on the crazy semantics of AT TIME ZONE - it should really be something like timestamp CONVERT FROM TIME ZONE '-5' and timestamptz CONVERT TO TIME ZONE '+5'. Also, timestamp with time zone should actually carry its timezone with it, not be stored in UTC and auto-converted to localtime.

Why the second works (so long as TimeZone = UTC)

Your original "works" version:

select '2011-12-30 00:30:00' AT TIME ZONE 'EST5EDT';

will only be correct if TimeZone is set to UTC, because the text-to-timestamptz cast assumes TimeZone when one isn't specified.

Why the third one works

Two problems cancel each other out.

The other version that appears to work is TimeZone independent, but it only works because two problems cancel themselves out. First, as explained above, timestamp without time zone AT TIME ZONE re-interprets the timestamp as being in that time zone for conversion to a UTC timestamptz; this effectively subtracts the timezone offset.

However, for reasons I beyond my ken, PostgreSQL uses timestamps with the reverse sign to what I'm used to seeing most places. See the documentation:

Another issue to keep in mind is that in POSIX time zone names, positive offsets are used for locations west of Greenwich. Everywhere else, PostgreSQL follows the ISO-8601 convention that positive timezone offsets are east of Greenwich.

This means that EST5EDT is the same as +5, not -5. Which is why it works: because you're subtracting the tz offset not adding it, but you're subtracting a negated offset!

What you'd need to get it correct is instead:

select TIMESTAMP '2011-12-30 00:30:00' AT TIME ZONE 'UTC'
       AT TIME ZONE '+5';

Blancmange

@RichardHuxton I've come up with some pretty dumb stuff in my time too, and I don't want someone coming after me with a bat ;-) so no. Ever been reading code, muttering things like "what dribbling idiot wrote this" ... then realised it was you? – Blancmange

I've written a lengthy article about timestamps and time zones in PostgreSQL that goes into further details. Might be helpful for someone landing here. – Monogenesis