generate_series for timestamptz and time zone problem
Przemysław Sztoch <przemyslaw@sztoch.pl>
From: Przemysław Sztoch <przemyslaw@sztoch.pl>
To: Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2022-05-31T19:54:05Z
Lists: pgsql-hackers
|generate_series| ( /|start|/ |timestamp with time zone|, /|stop|/
|timestamp with time zone|, /|step|/ |interval| )
produces results depending on the timezone value set:
SET timezone = 'UTC';
SELECT ts, ts AT TIME ZONE 'UTC'
FROM generate_series('2022-03-26 00:00:00+01'::timestamptz, '2022-03-30
00:00:00+01'::timestamptz, '1 day') AS ts;
SET timezone = 'Europe/Warsaw';
SELECT ts, ts AT TIME ZONE 'UTC'
FROM generate_series('2022-03-26 00:00:00+01'::timestamptz, '2022-03-30
00:00:00+01'::timestamptz, '1 day') AS ts;
Sometimes this is a very big problem.
The fourth argument with the time zone will be very useful:
|generate_series| ( /|start|/ |timestamp with time zone|, /|stop|/
|timestamp with time zone|, /|step|/ |interval| [, zone text] )
The situation is similar with the function timestamptz_pl_interval. The
third parameter for specifying the zone would be very useful.
--
Przemysław Sztoch | Mobile +48 509 99 00 66
Commits
-
Add functions to do timestamptz arithmetic in a non-default timezone.
- 75bd846b68b0 16.0 landed
-
Refactor datetime functions' timezone lookup code to reduce duplication.
- 3e59e5048d0f 16.0 landed
-
Fix volatility marking of timestamptz_trunc_zone.
- 533e02e927c6 16.0 landed