Why does postgresql differentiate between named and numeric time zone notations? 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:



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