Return to Question

edited body

Source Link

edited Apr 23 at 21:15

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?
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?

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?

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?

Source Link

asked Apr 23 at 21:14

markec

Postgres unexpected results with time zone conversions

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?