Re: Inconsistency of timezones in postgresql

On Wednesday, July 31, 2024, Aleksander Alekseev <aleksander@timescale.com>
wrote:

> Hi,
>
> > 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
> >
> > [...]
> >
> > Microsoft land has it:
> > https://learn.microsoft.com/en-us/sql/t-sql/queries/at-
> time-zone-transact-sql?view=sql-server-ver16
> >
> > [...]
>
> How other systems treat one case or another is not the best argument.
> We can't break our behaviour for the existing users and applications
> even if the correctness of this behavior is debatable.
>
> Initially I thought that we could address the issue by simply placing
> warnings like this:
>
> ```
> ereport(WARNING,
>     (errmsg("Using fixed offset with timestamp() / AT TIME ZONE),
>      errdetail("Offset is treated by POSIX rules instead of ISO ones"),
>      errhint("For better clarity use AT TIME ZONE 'UTC' + interval 'N
> hours'")));
> ```
>
>
This idea is a non-starter.  We don’t warn on usage generally, and
especially not in queries.


>
> Perhaps we should address this by simply adding a bold warning to the
> documentation with the advice to use "AT TIME ZONE 'UTC' + interval 'N
> hours" if the user really needs this.


This probably should be mentioned more prominently - but the UTC constant I
believe is unnecessary.


>
> Alternatively we could provide timezone_iso(text, timestamp[tz])
> functions that just replace all the +'s to -'s and vice versa in its
> first argument and then calls timezone().
>

It detracts from the “use names, not numbers” position we’ve taken and
doesn’t really help the casual user.  Overall not convinced this is the
right approach.

I’d rather special-case a new syntax here if we do anything code-wise.

{I|P}[-]HH:mm

I means interpret the sign by ISO conventions, P means by POSIX

David J.