Horton

Re: Inconsistency of timezones in postgresql

David G. Johnston <david.g.johnston@gmail.com>

View thread
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: Chris BSomething <xpusostomos@gmail.com>
Cc: Aleksander Alekseev <aleksander@timescale.com>, "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Date: 2024-07-31T14:35:27Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. doc: add example of sign mismatch with POSIX/ISO-8601 time zones

On Wednesday, July 31, 2024, Chris BSomething <xpusostomos@gmail.com> wrote:

>
> 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."
>


Right, we don’t claim to accept a “time zone offset” specification there
while they do.  Such a specification would be interpreted as ISO if we
could add it without conflicting with existing poorly written posix
specifications.

 This seems like bug though: (appendix)
*STD* *offset* [ *DST* [ *dstoffset* ] [ , *rule* ] ]

STD should be marked optional since apparently upon input its absence goes
unnoticed. The fact we don’t error if it is present but not in the form
<..> is also contributing to this problem.

An approach would be to enforce strict POSIX specifications and prohibit
any letters preceding the timezone offset; and we’d still shift the
incorrectly accepted and interpreted POSIX time zone offset string 12 hours.

David J.