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: Aleksander Alekseev <aleksander@timescale.com>
Cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>, Chris BSomething <xpusostomos@gmail.com>
Date: 2024-07-31T12:31:39Z
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, Aleksander Alekseev <aleksander@timescale.com>
wrote:

> Hi,
>
> > So I basically wasted a day's work trying to figure out what was going
> on, with queries like this:
> >
> > select change_time at time zone 'UTC+10' from mytable;
> >
> > and getting nonsense [...]
>
> I couldn't understand the bug report at first. Apparently the
> complaint is about the following behavior:
>
> Same for AT TIME ZONE 'UTC+3' | 'GMT+3' | 'Z+3'. I agree this is
> inconsistent and counterintuitive. On the flip side changing this
> behavior would mean breaking backward compatibility.
>
> Maybe we could come up with a good name for a function that would
> replace timezone() and recommend using it instead.
>

The bug is about the discoverability and imparting of the knowledge that
resides in our appendix regarding the fact that some timezone names map to
POSIX and while others map to ISO.

Our present behavior is to impart this knowledge at. Data types, date/time,
Timezones:

https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES

and elsewhere in the documentation do not address this aspect of time.

I’d be content with maybe promoting the third item on that subsection to a
warning…but I don’t really see us mentioning this more broadly than this
definitional section.

David J.