From: | Aleksander Alekseev <aleksander(at)timescale(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Undocumented behavior of timezone(zone, timestamp) for impossible timestamptz's |
Date: | 2022-01-10 12:04:28 |
Message-ID: | CAJ7c6TN7J+V=w1XbA25awSD+WVdtrOsZSWYGcwF3fSdV+7pT5g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2022-01-10 12:48:31 | Re: Add jsonlog log_destination for JSON server logs |
Previous Message | Juan José Santamaría Flecha | 2022-01-10 11:53:40 | Re: Fix vcregress plpython3 warning |