Search code examples
postgresqltimezone

Incorrect timezone with casting datetime string (earlier than 1948 year) to timestamptz or timetz

Linux or Windows, it's no matter.

PostgreSQL version from 14 to 15.3 at least.

set time zone 'Antarctica/Syowa';

select '1947-01-01 08:00:00'::timestamptz,
       '1948-01-01 08:00:00'::timestamptz,
       '1947-01-01 08:00:00'::timestamptz at time zone 'ETC/UTC';

Result: Result

So, it's just wrong time zone because right is +3 offset from UTC.

And it's moreover. Some timezones show incorrect result with different top right years:

Asia/Aqtau - earlier 1931 year

Indian/Comoro - earlier 1943 year

and so on...

What is going on with timezones?

Solution

  • So, it's just wrong time zone because right is +3 offset from UTC.

    No, that's not the case - at least, not according to the IANA time zone database which I suspect is what Postgres is using.

    I personally use my own Noda Time tzvalidate page to check these things, but that's based on the data which you can see in GitHub in Paul Eggert's repo - or download from IANA, but it's simpler to browse on GitHub.

    You can see there in the backward file that Antarctica/Syowa is a link to Asia/Riyadh, and in the asia file we can see the following rules:

    # Zone  NAME        STDOFF  RULES   FORMAT [UNTIL]
Zone    Asia/Riyadh 3:06:52 -       LMT    1947 Mar 14
                    3:00    -       %z

    So basically Postgres is doing exactly what I'd expect, for that data source.