Postgres timezone offsetting not working as expected

Question

I have a larger query that does not seem to be working correctly so I have reproduced the issue with this smaller select statement that seems to show the same odd behavior.

This select :

SELECT '2023-04-03T04:00:00.000Z'::timestamp at time zone 'UTC',
       '2023-04-03T04:00:00.000Z'::timestamp at time zone 'UTC' at time zone 'America/New_York',
       '2023-04-03T04:00:00.000Z'::timestamp at time zone 'UTC' at time zone 'America/New_York' at time zone 'UTC'

Should return the columns:

2023-04-03 04:00:00.000000
2023-04-03 08:00:00.000000
2023-04-03 04:00:00.000000

However it is returning

2023-04-03 04:00:00.000000
2023-04-03 08:00:00.000000
2023-04-03 08:00:00.000000

This makes no sense because it should be triple converting that last one. I have tried several variations of this including using the timestamptz data type and going from America/New_York back to UTC never works.

As mentioned above, I understand it seems stupid to be doing this, but part of a larger set of code needs to do something like this, primarily convert from a TZ back to UTC and it is not doing it properly. These queries are just simple tests that simulate what the much larger query is experiencing.

Answer 1

You expect it to convert the same time back and forth, but that's not how it works. You keep subtracting the time zone offset.

Also, your displayed result is simply wrong. The offset is missing from row 1 & 3. timestamptz values (timestamp with time zone) don't make sense without time zone. The display depends on the timezone setting of the current session, and would be:

SET timezone = 'America/New_York';

2023-04-03 00:00:00-04
2023-04-03 00:00:00
2023-04-02 20:00:00-04

Or:

SET timezone = 'Europe/Vienna';

2023-04-03 06:00:00+02
2023-04-03 00:00:00
2023-04-03 02:00:00+02

Both results are exactly the same, just displayed differently for different time zones.

timestamptz '2023-04-03 00:00:00-04' = timestamptz '2023-04-03 06:00:00+02'

Step-by-step

A given timestamp value, unaware of any time zone:

SELECT '2023-04-03T04:00:00.000Z'::timestamp
2023-04-03 04:00:00

The unique point in time when a clock in time zone 'UTC' shows that time.
The display is adapted to the timezone setting of the current session:

SELECT '2023-04-03T04:00:00.000Z'::timestamp AT TIME ZONE 'UTC'
2023-04-03 00:00:00-04

The local time in the given time zone displayed at that same point in time.
If 'America/New_York' is also the current timezone setting, it's the above after truncating the time zone offset:

SELECT '2023-04-03T04:00:00.000Z'::timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'AMERICA/NEW_YORK';
2023-04-03 00:00:00

The unique point in time when a clock in time zone 'UTC' shows that time Not the same!! We have subtracted the time zone offset. Again.

SELECT '2023-04-03T04:00:00.000Z'::timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'AMERICA/NEW_YORK' AT TIME ZONE 'UTC';
2023-04-02 20:00:00-04

See:

• Ignoring time zones altogether in Rails and PostgreSQL