Thread: 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

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.

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

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

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

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'")));
```

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

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 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?

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: 

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.

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

and when I use "AT TIME ZONE", it assumes the timestamp is UTC, and makes its calculations accordingly.

I do an ALTER COLUMN type timestamptz, and it adds a seemingly random 8 hours to the values... that's not a bug?

Hi,

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

IMO the warning should be placed closer to the documentation for AT
TIME ZONE where users have more chances to find it. Here is the patch.

The paragraph:

"""
In addition to the timezone names and abbreviations, PostgreSQL will
accept POSIX-style time zone specifications, as described in Section
B.5. This option is not normally preferable [...]
"""

... of section 8.5.3 looks reasonably well written to me and it gives
the reference to the corresponding section about POSIX rules. We could
additionally clarify this section with the following examples:

```
-- works as most users would expect
SELECT TIMESTAMPTZ '2024-08-01 13:06:20+3' AT TIME ZONE 'UTC';
Result: 2024-08-01 10:06:20

-- applies POSIX rules
SELECT TIMESTAMPTZ '2024-08-01 13:06:20 UTC-3' AT TIME ZONE 'UTC';
Result: 2024-08-01 10:06:20
```

However personally I believe it's redundant. In my humble experience
people typically prefer shorter syntax as in the first example and we
already said that we don't recommend using POSIX-style time zones. The
reason why it's important in case of AT TIME ZONE is because both '+3'
and 'UTC+3' are interpreted by POSIX rules.

-- 
Best regards,
Aleksander Alekseev

ibis=# set time zone 'UTC';

SET

ibis=# select update_date at time zone 'Australia/Sydney' from collection_item where item_id=2197;
        timezone
------------------------
 2012-07-06 02:59:55+00

And whatever it is doing, where is that documented?

> On Aug 1, 2024, at 21:55, Chris BSomething <xpusostomos@gmail.com> wrote:
> It says that it assumes that the "value is in the NAMED timezone". What actually happens is it assumes the value is
inyour environmental time zone, and DISPLAYS it in your current zone. 

No, that's not correct.  There are two separate conversions happening: first, it converts the TIMESTAMP value to a
TIMESTAMPTZvalue.  In doing so, it assumes TIMESTAMP value represents a timestamp at the time zone specified with AT
TIMEZONE.  Now, you have a TIMESTAMPTZ, which (internally) is in UTC.  When that is displayed, it's converted to the
sessiontimezone. 

xof=# select '2024-01-02 00:00'::timestamp;
      timestamp
---------------------
 2024-01-02 00:00:00
(1 row)


xof=# show timezone;
  TimeZone
------------
 US/Pacific
(1 row)

xof=# select ('2024-01-02 00:00'::timestamp) at time zone 'US/Pacific';
        timezone
------------------------
 2024-01-02 00:00:00-08
(1 row)

xof=# select ('2024-01-02 00:00'::timestamp) at time zone 'UTC';
        timezone
 ------------------------
 2024-01-01 16:00:00-08
(1 row)

xof=# set timezone = 'UTC';
SET
xof=# select ('2024-01-02 00:00'::timestamp) at time zone 'UTC';
        timezone
------------------------
 2024-01-02 00:00:00+00
(1 row)

This can indeed be confusing, but it works as documented.

Anyway, isn't the documentation wrong? 

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

timestamp without time zone AT TIME ZONE zone → timestamp with time zone
Converts given time stamp without time zone to time stamp with time zone, assuming the given value is in the named time zone.
timestamp '2001-02-16 20:38:40' at time zone 'America/Denver' → 2001-02-17 03:38:40+00

Unfortunately the doco doesn't tell us what environmental time zone it is assuming so the user is not enlightened by the examples.