Postgres unexpected results with time zone conversions

Question

I am doing some imports of data that includes time zone conversion. Data that I am importing is in America/New_York timezone and I have to import it as UTC timestamp.

I tried some examples I found on the internet, like:

    to_timestamp(tar.read_at_date || ' ' || tar.read_at_hour, 'MM/DD/YYYY HH24:MI')::timestamp without time zone at time zone 'America/New_York' at time zone 'utc'

and this seemed to work well for most of the data, however, I noticed some strange conversions at specific times, like Postgres is applying daylight saving for UTC time zone (???).

To quickly illustrate behavior, look at examples and results below:

select ('2023-03-25 22:00:00'::timestamp at time zone 'America/New_York' at time zone 'utc');
result: 2023-03-26 03:00:00.000

select ('2023-03-25 23:00:00'::timestamp at time zone 'America/New_York' at time zone 'utc');
result: 2023-03-26 03:00:00.000

I am running these queries on:

PostgreSQL [15.5
PostgreSQL 15.5 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit]

Also, to mention, in 2023, daylight saving time in New York started on Sunday, March 12, 2:00 am. What is interesting here is that on March 26th 2023 at 2am is when clocks go forward in Europe.

Any reasonable explanation why this happens?

Answer 1

The function to_timestamp() taking text returns timestamptz. It's not IMMUTABLE because it depends on the current timezone setting.

The input expression tar.read_at_date || ' ' || tar.read_at_hour does not seem to have an appended time zone offset. Th proper way to tell Postgres that your timestamps are in the America/New_York timezone, is to set the timezone accordingly (for the current session):

SET timezone = 'America/New_York';

SELECT to_timestamp('03/25/2023 22:00', 'MM/DD/YYYY HH24:MI');  -- that's all!

-- if SET timezone has changed the timezone setting, you may want to reset:
RESET timezone;

This is ambiguous:

I have to import it as UTC timestamp.

Neither Postgres timestamp type is "in" any time zone at all. See:

• Time zone storage in data type "timestamp with time zone"

The data type timestamp (timestamp without time zone) has no concept of time zones. It needs to be set in a given time zone to be meaningful as universal time.

The data type timestamptz (timestamp with time zone) is stored as UTC internally. Its text representation is formatted according to the timezone setting of the current sesson. But it's always the same point in time. For me (with timezone = 'Europe/Vienna'), timestamps are currently displayed with '+02' offset (thanks to the utterly useless "daylight saving time"). So:

SELECT timestamptz '2023-04-25 19:00:00-04';

.. is displayed as '2023-04-26 01:00:00+02'. Exactly the same value, only formatted for a different time zone.