Re: Inconsistency of timezones in postgresql

Fair point, I don't know..

On the other hand, Oracle has it..

https://docs.oracle.com/database/121/NLSPG/ch4datetime.htm#GUID-D8C7A7EB-A507-42A2-9B10-5301E822A7F2

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:
https://learn.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-ver16

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(at)timescale(dot)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
>