Re: Inconsistency of timezones in postgresql

Поиск
Список
Период
Сортировка
От Chris BSomething
Тема Re: Inconsistency of timezones in postgresql
Дата
Msg-id CADrHaBEV+rAH9samE7cEPv=AGjFj175JT_ncatvYLt4FfyXD6w@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Inconsistency of timezones in postgresql  (Aleksander Alekseev <aleksander@timescale.com>)
Ответы Re: Inconsistency of timezones in postgresql
Re: Inconsistency of timezones in postgresql
Список pgsql-bugs
Дерево обсуждения

Fair point, I don't know..

On the other hand, Oracle has it..


And if I interpret what it says there correctly (without my brain getting fuzzy)...

"Time zone offset: The string '(+|-)HH:MM' specifies a time zone as an offset from UTC. For example, '-07:00' specifies the time zone that is 7 hours behind UTC. For example, if the UTC time is 11:00 a.m., then the time in the '-07:00' time zone is 4:00 a.m."

I THINK that is saying it is NOT Posix, but ISO... or the opposite of what postgresql does... I'd like to argue therefore postgresql is "wrong", though no doubt that will make some people mad. In ISO land,  a negative offset has an earlier time than UTC, and a positive offset has a later time than UTC, so if UTC is 11am, and UTC-7 is 4 am (like America), then that's ISO format.

Microsoft land has it:

Now I can't see an explicit statement on whether that is Posix or ISO, however it does mention that zones are interpreted according to the windows registry:
KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones
and if you look in there, it says Vladivostok UTC+10, so almost certainly this is an ISO setup.

I'm starting to think that it's basically a defacto SQL standard, if not actually an SQL standard, and it should be ISO, not Posix. I'm tempted to argue that even UTC+- should be changed to conform.

Highly doubtful that any production code cares about doing that, but having sensible output is useful for ad hoc queries.

On Wed, 31 Jul 2024 at 21:23, Aleksander Alekseev <aleksander@timescale.com> wrote:
Hi,

> "The function timezone(zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone."
>
> https://www.postgresql.org/docs/17/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
>
> Documentation seems to think it is.

I don't see any mention of the standard. As I understand the
documentation merely says that timezone() corresponds to the AT TIME
ZONE SQL-syntax. Whether the syntax is standard or not is not clear.

Maybe it *is* in the standard but I don't have the right volume and/or
my copy is outdated (it's 2016). Closest thing I could find is section
"4.6.2 Datetimes" of the document "ISO-IEC 9075-2 Foundation" (~1700
pages). I couldn't find any mention of AT TIME ZONE (or timezone()
function) in this or any other documents I have.

In any case the question if AT TIME ZONE is a standard syntax or not
doesn't seem to be particularly relevant in the context of this bug
report.

--
Best regards,
Aleksander Alekseev

В списке pgsql-bugs по дате отправления: