Undocumented behavior of timezone(zone, timestamp) for impossible timestamptz's

Started by Aleksander Alekseevover 4 years ago3 messageshackers
Jump to latest
#1Aleksander Alekseev
aleksander@timescale.com

Hi hackers,

Due to DST and also changes in local laws, there could be gaps in
local time [1]https://en.wikipedia.org/wiki/Moscow_Time#Past_usage. For instance, 1 second after "2011-03-27 01:59:59 MSK"
goes "2011-03-27 03:00:00 MSK":

```
select (timestamptz '2011-03-27 01:59:59 MSK') at time zone 'MSK';
timezone
---------------------
2011-03-27 01:59:59
(1 row)

select ((timestamptz '2011-03-27 01:59:59 MSK') + interval '1 second')
at time zone 'MSK';
timezone
---------------------
2011-03-27 03:00:00
(1 row)
```

This makes '2011-03-27 02:00:00 MSK' an impossible timestamptz. I was
curious how `timezone(zone, timestamp)` aka `timestamp at time zone`
handles such dates and discovered that it seems to round impossible
dates to the nearest possible one:

```
set time zone 'Europe/Moscow';

select (timestamp '2011-03-27 01:00:00') at time zone 'MSK';
timezone
------------------------
2011-03-27 01:00:00+03
(1 row)

select (timestamp '2011-03-27 02:00:00') at time zone 'MSK';
timezone
------------------------
2011-03-27 01:00:00+03
(1 row)
```

I don't know what the SQL standard says about it, but personally, I
find this behavior very convenient. Although it doesn't seem to be
documented [2]https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT -- Best regards, Aleksander Alekseev.

So I have two questions:

1. Should this behavior be documented in the 9.9.4. AT TIME ZONE
section or maybe it's documented elsewhere and I just missed it?
2. Is it possible to detect an impossible timestamptz's for users who
wants stricter semantics? If there is a way I think it's worth
documenting as well.

[1]: https://en.wikipedia.org/wiki/Moscow_Time#Past_usage
[2]: https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT -- Best regards, Aleksander Alekseev
--
Best regards,
Aleksander Alekseev

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Aleksander Alekseev (#1)
Re: Undocumented behavior of timezone(zone, timestamp) for impossible timestamptz's

Aleksander Alekseev <aleksander@timescale.com> writes:

Due to DST and also changes in local laws, there could be gaps in
local time [1].

Yup.

1. Should this behavior be documented in the 9.9.4. AT TIME ZONE
section or maybe it's documented elsewhere and I just missed it?

https://www.postgresql.org/docs/current/datetime-invalid-input.html

2. Is it possible to detect an impossible timestamptz's for users who
wants stricter semantics? If there is a way I think it's worth
documenting as well.

Maybe convert back and see if you get an identical result?

regards, tom lane

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Tom Lane (#2)
Re: Undocumented behavior of timezone(zone, timestamp) for impossible timestamptz's

I wrote:

Aleksander Alekseev <aleksander@timescale.com> writes:

1. Should this behavior be documented in the 9.9.4. AT TIME ZONE
section or maybe it's documented elsewhere and I just missed it?

https://www.postgresql.org/docs/current/datetime-invalid-input.html

... and reading that again, I realize that I screwed up the
fall-back example :-(. 2:30 is not ambiguous; I should have
demonstrated the behavior for, say, 1:30. Will fix.

regards, tom lane