3

Why is there a difference between named timezone and numeric timezone offset, both of which yield different UTC timestamps for TIMESTAMPTZ?

Assume Europe/Prague timezone, which is currently +02:00 (DST); time of writing is 2018-08-25.

As you can check e.g. via google:

uploaded image

Also, in my postgres installation I can query it:

SELECT utc_offset FROM pg_timezone_names where name = 'Europe/Prague';

Which also yields 02:00:00.

Then, why on earth, do I get two different UTC timestamps when running following two queries?

The following yields 2018-08-25T20:00:00.000Z (2018-08-25 18:00:00-02):

SET TIME ZONE '+02:00'; -- with or without the plus
SELECT '2018-08-25 18:00:00'::TIMESTAMPTZ;

The following yields 2018-08-25T16:00:00.000Z (2018-08-25 18:00:00+02), which is correct:

SET TIME ZONE 'Europe/Prague';
SELECT '2018-08-25 18:00:00'::TIMESTAMPTZ;

Why does postgres differentiate those two notations?

Improve this question
1
  • 2
    DST (daylight saving time) changes according to countries. For example, 2 different countries with same offset but one uses DST and the other does not.
    – Sahap Asci
    Aug 25, 2018 at 22:11

1 Answer 1

Reset to default
1

Because, as documented, one uses POSIX and other uses ISO-8601.

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.

Improve this answer
2
  • But docs say that POSIX is in format STDoffset, so +02:00 shouldn't be POSIX, which is corroborated by docs here, which say that it's ISO-8601. Then why does it result in -02 UTC offset, which is incorrect (my first example in OP)?
    – dwelle
    Aug 26, 2018 at 7:54
  • Accepting since you're right, but I made a follow-up question, to figure out why this is so.
    – dwelle
    Aug 26, 2018 at 12:16

Not the answer you're looking for? Browse other questions tagged or ask your own question.