Postgres unexpected results with time zone conversions
0
votes
1
answer
190
views
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?
Asked by markec
(3 rep)
Apr 23, 2024, 09:14 PM
Last activity: May 3, 2024, 09:39 PM
Last activity: May 3, 2024, 09:39 PM