Horton

Re: Inconsistency of timezones in postgresql

Aleksander Alekseev <aleksander@timescale.com>

View thread
From: Aleksander Alekseev <aleksander@timescale.com>
To: pgsql-bugs@lists.postgresql.org
Cc: Chris BSomething <xpusostomos@gmail.com>
Date: 2024-07-31T11:50:23Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. doc: add example of sign mismatch with POSIX/ISO-8601 time zones

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