0

According to IANA docs here https://data.iana.org/time-zones/tz-how-to.html the America/Chicago time zone has a time change at '1920-10-31 02:00:00' for daylight saving. Postgres doesn't account for these rules when generating a series of timestamps.

Note All queries tested against Postgres 9.6.

In this base case that doesn't generate a series, the above IANA America/Chicago rule is accurately reflected in Postgres:

select 
timestamptz '1920-10-31 01:00:00 America/Chicago',
timestamptz '1920-10-30 01:00:00 America/Chicago' + interval '24 hours'

Results in

base case results

Notice the second timestamp loses an hour due to the America/Chicago time zone rule for daylight saving.

Unfortunately, extrapolating this behavior to any sort of loop/function doesn't replicate the above expected behavior. I've tested with the following three ways:

generate_series:

select * from generate_series(timestamptz '1920-10-29 01:00:00 America/Chicago', timestamptz '1920-11-02 01:00:00 America/Chicago', interval '24 hours')

Results in

generate_series results

recursive common table expression:

WITH RECURSIVE timestamp_series AS (
SELECT timestamptz '1920-10-29 01:00:00 America/Chicago' AS generated_timestamp
UNION ALL
SELECT generated_timestamp + interval '24 hours'
FROM timestamp_series
WHERE generated_timestamp + interval '24 hours' <= timestamptz '1920-11-02 01:00:00 America/Chicago'
)
SELECT generated_timestamp FROM timestamp_series;

Results in

recursive common table expression results

plpgsql function:

CREATE OR REPLACE FUNCTION generate_timestamp_series(
start_timestamp timestamptz,
end_timestamp timestamptz,
timestamp_interval interval
)
RETURNS SETOF timestamptz AS
$$
DECLARE
loop_timestamp timestamptz := start_timestamp;
BEGIN
WHILE loop_timestamp <= end_timestamp LOOP
RETURN NEXT loop_timestamp;
loop_timestamp := loop_timestamp + timestamp_interval;
END LOOP;

RETURN;
END;
$$
LANGUAGE plpgsql;

SELECT * FROM generate_timestamp_series(
timestamptz '1920-10-29 01:00:00 America/Chicago',
timestamptz '1920-11-02 01:00:00 America/Chicago',
interval '1 day'
);

Results in

plpgsql function results

I'd expect to see the daylight saving rule to cause one of the records to lose an hour, but that doesn't happen. Is it possible to create a series of timestamps accounting for time zone changes in Postgres?

S Strong
  • 1
  • 1
  • 1
    1) Do not use images, copy and paste text. 2) Do `select a at time zone 'America/Chicago' from generate_series(timestamptz '1920-10-29 01:00:00', timestamptz '1920-11-02 01:00:00 ', interval '24 hours') as t(a);` 3) Read [Date/times](https://www.postgresql.org/docs/current/datatype-datetime.html) section *8.5.1.3. Time Stamps*. Your queries are doing what you think they as you are not taking into account the server timezone shown by `show timezone`. – Adrian Klaver Jul 28 '23 at 00:42
  • [`with timezone` is a misnomer](https://stackoverflow.com/questions/5876218/difference-between-timestamps-with-without-time-zone-in-postgresql), the timezone is not part of the value. – Bergi Jul 28 '23 at 01:49
  • Unlike Oracle, in PostgreSQL the time zone is not stored in a `TIMESTAMPTZ` value. It's internally processed and converted and saved as UTC. – The Impaler Jul 28 '23 at 12:02
  • @AdrianKlaver this fixes my issue, thank you. And yes, that is explicitly mentioned in the docs; I simply overlooked it: `When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct` – S Strong Jul 28 '23 at 16:15

0 Answers0