Re: Inconsistency of timezones in postgresql - Mailing list pgsql-bugs

From Aleksander Alekseev
Subject Re: Inconsistency of timezones in postgresql
Date
Msg-id CAJ7c6TNrxqZVNx2ob2-x5wMP3WHtyR6xKUZJjNuKFNuHs6XMsA@mail.gmail.com
Whole thread Raw
In response to Re: Inconsistency of timezones in postgresql  (Chris BSomething <xpusostomos@gmail.com>)
Responses Re: Inconsistency of timezones in postgresql
Re: Inconsistency of timezones in postgresql
List pgsql-bugs
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'")));
```

... somewhere in timestamp_zone() and timestamptz_zone() but I discovered that:

```
AT TIME ZONE 'UTC+3'
```

... and

```
AT TIME ZONE 'Europoe/Moscow'
```

... actually take the same code path ( DecodeTimezoneName() returns
TZNAME_ZONE ) so unfortunately it's not going to be as trivial as
that.

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.

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().

Thoughts?

-- 
Best regards,
Aleksander Alekseev



pgsql-bugs by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: Inconsistency of timezones in postgresql
Next
From: Tom Lane
Date:
Subject: Re: Inconsistency of timezones in postgresql