Inconsistency of timezones in postgresql

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:

```
-- note: MSK is UTC+3
-- as expected
SELECT ('2024-07-31 12:34:56 MSK' :: timestamptz) AT TIME ZONE 'MSK';
timezone
---------------------
2024-07-31 12:34:56

-- as expected
SELECT ('2024-07-31 12:34:56+3' :: timestamptz) AT TIME ZONE 'MSK';
timezone
---------------------
2024-07-31 12:34:56

-- as expected
SELECT timezone('MSK', '2024-07-31 12:34:56+3');
timezone
---------------------
2024-07-31 12:34:56

-- nonsense
SELECT ('2024-07-31 12:34:56+3' :: timestamptz) AT TIME ZONE '+3';
timezone
---------------------
2024-07-31 06:34:56

-- nonsense
SELECT timezone('+3', '2024-07-31 12:34:56+3');
timezone
---------------------
2024-07-31 06:34:56
```

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.

Thoughts?

" Another issue to keep in mind is that in POSIX time zone names, positive offsets are used for locations west of Greenwich. Everywhere else, PostgreSQL follows the ISO-8601 convention that positive timezone offsets are east of Greenwich."

This quote seems to be from the previous versions of the
documentation. Unless I missed something this behavior of timezone() /
AT TIME ZONE is currently not documented.

--
Best regards,
Aleksander Alekseev

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.

The quote is from here:

https://www.postgresql.org/docs/8.3/datatype-datetime.html

I guess if it's no longer even in the documentation, that's even worse.

The AT TIME ZONE syntax is mentioned here... with little detail, certainly
without warning you of this confusion...

https://www.postgresql.org/docs/17/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

Yes, the issue is, the input expects hours east of GMT and the output is
hours west of GMT. The former is Posix, the latter is ISO.

I was referring to the AT TIME ZONE syntax, I'm sure there are other places
like you say the timezone() function.

I suggested that using "Z" for ISO interpretation (hours west of GMT) would
be minimal compatibility damage because the Posix (hours east of GMT)
documentation doesn't mention Z for UTC, it says things like UTC+10.

I don't think merely thinking of a new function name is good enough because
"AT TIME ZONE" I believe is an SQL standard. Now I don't have the SQL
standard because I don't want to shell out the crazy money they want for
it, but from my research, I don't believe the SQL standard says what format
the time zone should be in, so you are stuck with the situation of
inventing some string format that both accepts the current common things
people use, and also accepts something more sensible, aka ISO formats. On
the bright side, not that many people use this AT TIME ZONE feature, the
people who do use it for the most part will use geographic names, whIch
makes much more sense for serious use ( e.g. America/New_York ), the few
people who use offsets will be saying UTC-10 or whatever... which I would
argue leaves Z+10 as something that wouldn't interfere with anyone, yet
would give people an ISOish style AND substance for their zone offsets, and
we can deprecate UTC+- for normal people's use. And if there's one guy out
there using Z+- and expecting Posix, tough luck... should have read the
documentation.

On Wed, 31 Jul 2024 at 19:50, Aleksander Alekseev <aleksander@timescale.com>
wrote:

Hi,

I don't think merely thinking of a new function name is good enough because "AT TIME ZONE" I believe is an SQL standard. Now I don't have the SQL standard because I don't want to shell out the crazy money they want for it [...]

From what I can tell AT TIME ZONE syntax is not a part of the SQL
standard. The standard describes only implicit casts between TIMESTAMP
WITH TIMEZONE and TIMESTAMP WITHOUT TIMEZONE.

--
Best regards,
Aleksander Alekseev

"The function timezone(zone, timestamp) is equivalent to the SQL-conforming
construct timestamp AT TIME ZONE zone."

https://www.postgresql.org/docs/17/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

Documentation seems to think it is.

On Wed, 31 Jul 2024 at 20:52, Aleksander Alekseev <aleksander@timescale.com>
wrote:

Hi,

"The function timezone(zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone."

https://www.postgresql.org/docs/17/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

Documentation seems to think it is.

I don't see any mention of the standard. As I understand the
documentation merely says that timezone() corresponds to the AT TIME
ZONE SQL-syntax. Whether the syntax is standard or not is not clear.

Maybe it *is* in the standard but I don't have the right volume and/or
my copy is outdated (it's 2016). Closest thing I could find is section
"4.6.2 Datetimes" of the document "ISO-IEC 9075-2 Foundation" (~1700
pages). I couldn't find any mention of AT TIME ZONE (or timezone()
function) in this or any other documents I have.

In any case the question if AT TIME ZONE is a standard syntax or not
doesn't seem to be particularly relevant in the context of this bug
report.

--
Best regards,
Aleksander Alekseev

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

I THINK that is saying it is NOT Posix, but ISO... or the opposite of what
postgresql does... I'd like to argue therefore postgresql is "wrong",
though no doubt that will make some people mad. In ISO land, a negative
offset has an earlier time than UTC, and a positive offset has a later time
than UTC, so if UTC is 11am, and UTC-7 is 4 am (like America), then that's
ISO format.

Microsoft land has it:
https://learn.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-ver16

Now I can't see an explicit statement on whether that is Posix or ISO,
however it does mention that zones are interpreted according to the windows
registry:
KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones
and if you look in there, it says Vladivostok UTC+10, so almost certainly
this is an ISO setup.

I'm starting to think that it's basically a defacto SQL standard, if not
actually an SQL standard, and it should be ISO, not Posix. I'm tempted to
argue that even UTC+- should be changed to conform.

Highly doubtful that any production code cares about doing that, but having
sensible output is useful for ad hoc queries.

On Wed, 31 Jul 2024 at 21:23, Aleksander Alekseev <aleksander@timescale.com>
wrote:

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.

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

Aleksander Alekseev <aleksander@timescale.com> writes:

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

That will add confusion, not reduce it.

Thoughts?

I think this is a documentation issue, specifically that Section 8.5.3
is not sufficiently in-your-face about "UTC+2" not meaning what you
probably think. We didn't really do anybody any favors by shoving
those details off to Appendix B.

regards, tom lane

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.

Aleksander Alekseev <aleksander@timescale.com> writes:

I don't see any mention of the standard. As I understand the
documentation merely says that timezone() corresponds to the AT TIME
ZONE SQL-syntax. Whether the syntax is standard or not is not clear.

The syntax has been there since SQL92:

6.14 <datetime value expression>

Function

Specify a datetime value.

Format

<datetime value expression> ::=
<datetime term>
| <interval value expression> <plus sign> <datetime term>
| <datetime value expression> <plus sign> <interval term>
| <datetime value expression> <minus sign> <interval term>

<datetime term> ::=
<datetime factor>

<datetime factor> ::=
<datetime primary> [ <time zone> ]

<datetime primary> ::=
<value expression primary>
| <datetime value function>

<time zone> ::=
AT <time zone specifier>

<time zone specifier> ::=
LOCAL
| TIME ZONE <interval value expression>

However, notice that the value following TIME ZONE is only allowed to
be an interval by the spec (and this is still true in SQL:2021,
the latest version I have handy). Such an interval is interpreted per
ISO (positive = east of Greenwich). We allow that too, but we extend
the standard to allow time zone names as well.

The problem comes down to a very ancient decision to allow POSIX
zone strings as time zone names. We are not entirely to blame on
that, because the code involved is borrowed from the IANA tzcode
distribution. If you experiment, you will find out that common
Unix utilities interpret TZ the same way:

$ psql
psql (18devel)
Type "help" for help.

regression=# select now() at time zone 'America/New_York';
timezone
----------------------------
2024-07-31 11:32:12.089097
(1 row)

regression=# select now() at time zone 'UTC+2';
timezone
----------------------------
2024-07-31 13:32:14.399523
(1 row)

regression=# \q
$ TZ=America/New_York date
Wed Jul 31 11:32:23 EDT 2024
$ TZ=UTC+2 date
Wed Jul 31 13:32:26 UTC 2024

So whether you like it or not, it's pretty standard behavior.
There is zero chance that we'll change it.

regards, tom lane

Tom Lane said:
"However, notice that the value following TIME ZONE is only allowed to
be an interval by the spec (and this is still true in SQL:2021,
the latest version I have handy). Such an interval is interpreted per
ISO (positive = east of Greenwich)."

Erm, what do you mean by an interval? If you mean a number, then its
broken, because "UTC+10" and "+10" do the same thing. But you seem to be
saying there is indeed some syntax that is interpreted by ISO logic?

On Wed, 31 Jul 2024 at 23:34, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

Tom Lane said:
"However, notice that the value following TIME ZONE is only allowed to
be an interval by the spec (and this is still true in SQL:2021,
the latest version I have handy). Such an interval is interpreted per
ISO (positive = east of Greenwich)."

Erm, what do you mean by an interval? If you mean a number, then it’s
broken, because "UTC+10" and "+10" do the same thing. But you seem to be
saying there is indeed some syntax that is interpreted by ISO logic?

There is a named data type called “interval”. He’s referring to that.
Neither of those text values is an interval. ‘4 hours 30
minutes’::interval is a relevant example.

David J.

Well... I guess then at least we have...

AT TIME ZONE INTERVAL '8 hours'

or indeed...

AT TIME ZONE INTERVAL '+8h'

so at a bare minimum we need documentation that promotes that, and warns
about UTC+-

I still think Z+- would be a few lines of code that would be a cool fix
that wouldn't hurt anyone, but anyway.

Chris

On Thu, 1 Aug 2024 at 01:42, David G. Johnston <david.g.johnston@gmail.com>
wrote:

Riddle me this... I have a field of type timestamp. I do 3 queries on a
particular record one is AT TIME ZONE 'UTC', ones is AT TIME ZONE
Australia/Sydney, one is plain, without a clause and I get:

change_time timestamp not null default current_timestamp,

UTC: 2024-01-01 10:42:13
Australia/Sydney: 2024-01-01 00:42:13:+00
plain: 2024-01-01 10:42:13

Now I alter table and I alter column change_time type timestamp with time
zone, so I add the "with time zone clause", now its type is "timestamptz",
both timestamp and "with time zone" are both stored in UTC right? So
nothing important should change by altering the column should it? But now I
get:

UTC: 2024-01-01 02:42:13+00
Australia/Sydney: 2024-01-01 12:42:13
plain: 2024-01-01 02:42:13

So all the timestamps are now different. How can that be?

And to make matters worse, as I write it is 2024-08-01 01:26 UTC ... or in
other words BOTH scenarios return a date in the future, even though the
field is populated with a default clause of "current_timestamp". select
now() at time zone UTC returns the correct thing.

Is it wrong to assign current_timestamp to a timestamp field because
current_timestamp is "with time zone"? If so, that's amazing since I
thought under the hood its all UTC. I can't see that mentioned in the
documentation that using current_timestamp can be so dangerously wrong when
assigned to a timestamp.

And why does the "timestamp" datatype selected "with time zone
Australia/Sydney" throw a "+00" on the output? I don't understand why that
scenario in particular gets any +- on the output, but if anything it should
be "+10" right?

And why did the numbers shift 8 hours later when I changed the data type
of the table? I happen to be 8 hours east of GMT... but I didn't create
this record, if anything it was created by a program and person 10 hours
east. If I do "set time zone 'UTC'" in psql, it changes nothing, so it
doesn't seem to be anything in the psql client that causes the offset.

On Thu, 1 Aug 2024 at 03:03, Chris BSomething <xpusostomos@gmail.com> wrote:

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

Riddle me this... I have a field of type timestamp. I do 3 queries on a
particular record one is AT TIME ZONE 'UTC', ones is AT TIME ZONE
Australia/Sydney, one is plain, without a clause and I get:

We are no longer in bug reporting territory. Depesz wrote a good article
on all this a while back. I’d suggest reading it then following up further
on the -general mailing list if you still have questions.

https://www.depesz.com/2014/04/04/how-to-deal-with-timestamps/

David J.

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

Is it wrong to assign current_timestamp to a timestamp field because
current_timestamp is "with time zone"? If so, that's amazing since I
thought under the hood its all UTC. I can't see that mentioned in the
documentation that using current_timestamp can be so dangerously wrong when
assigned to a timestamp.

Using timestamp without time zone is really the issue here. There is even
a “don’t do this” entry for it.

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29_to_store_UTC_times

David J.

So... the wiki (not the documentation mind you) says "don't use timestamp"
because its there for compatibility with other databases?

So in what database can you set a timestamp to current_timestamp, then
display it raw, and it's in the future? That's not a bug?

Am I supposed to assign it localtimestamp, because that's not documented
anywhere. And why would it be? Timestamp is an undefined timezone, not a
localtime. At least that wiki article you linked says that, And since we're
told timezones with time zone is UTC internally, why wouldn't assigning it
result in current UTC time? By what logic does it end up in the future? If
there's logic to it, where is that documented? In fact that article says
"people from other databases store UTC times in timestamps", so if
timestamps are there for compatibility with people storing UTC, as it says,
then we should expect timestamps to probably have UTC in them... and when I
use "AT TIME ZONE", it assumes the timestamp is UTC, and makes its
calculations accordingly. So why would I expect localtime to ever raise
its ugly head here?

I do an ALTER COLUMN type timestamptz, and it adds a seemingly random 8
hours to the values... that's not a bug? There is no rhyme or reason for 8
hours to change. Maybe 10 hours, you can make some convoluted argument,
but 8 ? And since "for compatibility" we expect UTC as the most likely
contents of timestamp, why would anything be added to it?

And in any case, the local time of our postgres server is UTC. This record
is created by a trigger in the server. Shouldn't it be UTC in any case?
Even doing "set time zone 'UTC'" in psql doesn't bring more sanity.

And asking a timestamp to display in Australia/Sydney puts +00 on the
output? That's not a bug? What does it mean then? I'm not in
Australia/Sydney, so it can't mean it's +00 from that. The postgres server
is not in that zone, so it can't mean that. The documentation says
timezones are never stored in the data, so it can't mean the data was
created at that zone.

I read that article.... which basically tells us the virtues of
timestamptz, but doesn't say anything about how bad timestamp is other than
the timezone isn't explicit. that's great and all, but if timestamp is as
horribly broken in every respect... even in compatibility with other
databases, just remove the feature. It's unusable. There should be THIS IS
HORRIBLY BROKEN AND SHOULD NEVER BE USED IN ANY SHAPE OR FORM warnings all
over the place in red. And then document all this weird behavior that makes
no sense whatsoever.

On Thu, 1 Aug 2024 at 09:58, David G. Johnston <david.g.johnston@gmail.com>
wrote: