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.
Comments on SQL timestamp for daylight saving day when clock goes 1 hour back.
Post
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 comment thread