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.
