Обсуждение: Undocumented behavior of timezone(zone, timestamp) for impossible timestamptz's
Undocumented behavior of timezone(zone, timestamp) for impossible timestamptz's
От
Aleksander Alekseev
Дата:
Hi hackers, Due to DST and also changes in local laws, there could be gaps in local time [1]. 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]. 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
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
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