Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
SQL timestamp for daylight saving day when clock goes 1 hour back.
On 25th October 2020 in Europe/Berlin clocks where set back from 03:00 AM to 02:00 AM to change from summer time (CEDT) to winter time (CET).
Which means there is a 1 hour separation between 02:30 AM CEDT and 02:30 AM CET.
And, more confusing, 1 hour separation between 02:30 AM Europe/Berlin (summer time) and 02:30 AM Europe/Berlin (winter time).
Which leads to some trouble when giving a date&time in PostgreSQL
db=# CREATE TABLE TEST ( thetime timestamp with time zone );
db=# INSERT INTO TEST(thetime) VALUES('2020-10-25 01:30:00 Europe/Berlin');
db=# INSERT INTO TEST(thetime) VALUES('2020-10-25 02:30:00 Europe/Berlin');
db=# INSERT INTO TEST(thetime) VALUES('2020-10-25 03:30:00 Europe/Berlin');
db=# SELECT thetime AT TIME ZONE 'UTC' from TEST;
timezone
-------------
2020-10-24 23:30:00
2020-10-25 01:30:00
2020-10-25 02:30:00
(3 rows)
The 2nd INSERT has taken the winter time. Since I did not specify if I wanted the summer or winter time for 2:30 AM I guess it is ok for PSQL to chose. So I try to insert the summer time 2:30 AM
db=# INSERT INTO TEST(thetime) VALUES('2020-10-25 02:30:00 CEDT');
ERROR: invalid input syntax for type timestamp with time zone: "2020-10-25 02:30:00 CEDT"
LINE 1: INSERT INTO TEST(thetime) VALUES('2020-10-25 02:30:00 CEDT')...
^
db=# INSERT INTO TEST(thetime) VALUES('2020-10-25 00:30:00 UTC');
db=# SELECT thetime AT TIME ZONE 'UTC' from TEST;
timezone
-------------
2020-10-24 23:30:00
2020-10-25 01:30:00
2020-10-25 02:30:00
2020-10-25 00:30:00
(4 rows)
So I've only managed to insert 02:30 AM CEDT by using UTC. But if I have to write code to check for this special case and converting into UTC in such case then I am doing the same effort as dealing myself with time zones rather than letting PostgresSQL deal with such time zones.
Even using CEDT if it was allowed would be bad since then I need code to know when should I use CET or CEDT.
So the question is. Is there a way to specify a time&date that in these situations of change from summer to winter time will always take the earliest hour (the summer one)? Rather than taking the latest hour (the winter one) which is what PostgreSQL seems to be doing; and I have no confidence in this behaviour being guaranteed since https://www.postgresql.org/docs/12/datatype-datetime.html says nothing about this.
In other words, I am asking for doing in PostgreSQL (or even better generic SQL) something similar to C++20:
zoned_seconds thetime{"Europe/Berlin",
local_days{October/25/2020} + 2h + 30min,
choose::earliest };
Something else which worries me is this:
db=# CREATE TABLE TEST ( thetime timestamp with time zone );
db=# INSERT INTO TEST(thetime) VALUES('2020-10-25 00:30:00 UTC');
db=# INSERT INTO TEST(thetime) VALUES('2020-10-25 01:30:00 UTC');
db=# SELECT thetime AT TIME ZONE 'Europe/Berlin' from TEST;
timezone
---------------------
2020-10-25 02:30:00
2020-10-25 02:30:00
(2 rows)
Ouch, they show the same time despite being different ones. Is there any way to write that SELECT so that the time zone (CET or CEDT) shows?
Examples done with PostgreSQL 12.6
1 answer
This behaviour is documented here:
"... an ambiguous timestamp that could fall on either side of a jump-back transition is assigned the UTC offset that prevailed just after the transition."
So, when there's an ambiguous timestamp (when a local date/time occurs twice: first in Daylight Saving Time, and then in Standard Time), Postgres chooses the Standard Time offset.
The same documentation linked above says that the only way to force a different behaviour is to specify that you want DST. And you almost got it right, because for Europe/Berlin timezone, the DST abbreviation is "CEST", not "CEDT".
So, this should work:
INSERT INTO TEST(thetime) VALUES('2020-10-25 02:30:00 CEST'::timestamptz);
Another alternative is to use the offset instead of the abbreviation:
INSERT INTO TEST(thetime) VALUES('2020-10-25 02:30:00 +01:00'::timestamptz); -- Standard Time
INSERT INTO TEST(thetime) VALUES('2020-10-25 02:30:00 +02:00'::timestamptz); -- DST
Note that I forced the type to be a TIMESTAMP WITH TIME ZONE (note: timestamptz is a PostgreSQL extension, which is equivalent to TIMESTAMP WITH TIME ZONE). That's because the documentation says:
PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat ... as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type
To know more about the difference between timestamps with and without timezone, check here.
But obviously you'll need to know the exact offsets that a particular timezone has at Standard Time and DST, in order to have an unambiguous value. There seems to be no way to configure it (Postgres will always choose Standard Time), and you'll have to handle it manually somehow (most programming languages have API's to deal with it and generate the correct data).
As a side note, when there's a "spring forward" (clocks are set 1 hour forward, so a whole hour is "skipped", therefore there's a gap of non-existent local time), Postgres makes an adjustment.
For example, in America/New_York timezone, 2018-03-11 02:30 represents a non-existent local time (because clocks jumped forward from 01:59 to 03:00, due to DST transition). In that case, Postgres adjust the timestamp to 2018-03-11 03:30:00-04 (considering that America/New_York timezone is in use - the exact date/time when this happens will vary according to each timezone's rules).
If you want a different adjustment, you'll have to make it outside the database, in order to insert the desired data.
Regarding displaying the dates in another format, you can set datestyle according to this table. But this is very limited, as there aren't many options available.
If you want to customize the format, the only way is to use the formatting functions, such as to_char:
SELECT to_char(thetime, 'YYYY-MM-DD HH24:MI:SS OF TZ') from TEST;
This would output the values like this:
2020-10-25 02:30:00 +01 CET
2020-10-25 02:30:00 +02 CEST
0 comment threads
1 comment thread