Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics

Dashboard
Notifications
Mark all as read
Q&A

SQL timestamp for daylight saving day when clock goes 1 hour back.

+7
−0

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

Why does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

3 comments

I always normalize timezones in the application tier because I don't like to depend on DBMS-specific logic for finicky stuff like this (in addition, I know Java's data time libraries much better than SQL's). meriton‭ 17 days ago

A quick "frame challenge": Why not always insert a UTC timestamp in the table and display the local time only when needed? Alexei‭ 17 days ago

@Alexei Displaying is not enough. I also need to read local time from users and insert them in the table. From users at different time zones. And I'd prefer to reuse time zone conversion code rather than having to write it myself. From hkotsubo's answer I've come to the conclusion that PostgreSQL is not a good fit for that task because it is documented as having no way to specify "2:30 AM take earliest if ambiguous". So I'll do that at C++ level (with Howard Hinnant's lib) and indeed UTC for DB. Estela‭ 15 days ago

1 answer

+7
−0

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
Why does this post require moderator attention?
You might want to add some details to your flag.

0 comments

Sign up to answer this question »