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.

Post History

81%

+7 −0

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

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." S...

posted 3y ago by hkotsubo‭ · edited 3y ago by hkotsubo‭

Answer

#2: Post edited by

hkotsubo‭ · 2021-03-29T13:21:31Z (over 3 years ago)

Copy Link

Raw

Markdown

This behaviour is documented [here](https://www.postgresql.org/docs/12/datetime-invalid-input.html):
> "*... 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"](https://www.timeanddate.com/time/zones/cest).
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](https://www.postgresql.org/docs/12/datatype-datetime.html) 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_
<sup>To know more about the difference between timestamps with and without timezone, [check here](https://stackoverflow.com/a/13231920).</sup>
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).
---
Regarding displaying the dates in another format, you can set `datestyle` according to [this table](https://www.postgresql.org/docs/12/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT). 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`](https://www.postgresql.org/docs/12/functions-formatting.html):
SELECT to_char(thetime, 'YYYY-MM-DD HH24:MI:SS OF TZ') from TEST;
This would output the values like this:
```none
2020-10-25 02:30:00 +01 CET
2020-10-25 02:30:00 +02 CEST
```

This behaviour is documented [here](https://www.postgresql.org/docs/12/datetime-invalid-input.html):
> "*... 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"](https://www.timeanddate.com/time/zones/cest).
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](https://www.postgresql.org/docs/12/datatype-datetime.html) 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_
<sup>To know more about the difference between timestamps with and without timezone, [check here](https://stackoverflow.com/a/13231920).</sup>
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](https://www.postgresql.org/docs/12/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT). 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`](https://www.postgresql.org/docs/12/functions-formatting.html):
SELECT to_char(thetime, 'YYYY-MM-DD HH24:MI:SS OF TZ') from TEST;
This would output the values like this:
```none
2020-10-25 02:30:00 +01 CET
2020-10-25 02:30:00 +02 CEST
```

#1: Initial revision by

hkotsubo‭ · 2021-03-25T13:13:26Z (over 3 years ago)

Copy Link

Raw

Markdown

This behaviour is documented [here](https://www.postgresql.org/docs/12/datetime-invalid-input.html):

> "*... 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"](https://www.timeanddate.com/time/zones/cest).

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](https://www.postgresql.org/docs/12/datatype-datetime.html) 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_

<sup>To know more about the difference between timestamps with and without timezone, [check here](https://stackoverflow.com/a/13231920).</sup>

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).

---
Regarding displaying the dates in another format, you can set `datestyle` according to [this table](https://www.postgresql.org/docs/12/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT). 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`](https://www.postgresql.org/docs/12/functions-formatting.html):

    SELECT to_char(thetime, 'YYYY-MM-DD HH24:MI:SS OF TZ') from TEST;

This would output the values like this:

```none
2020-10-25 02:30:00 +01 CET
2020-10-25 02:30:00 +02 CEST
```

Communities

Post History