Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?

Setting time zone as numeric offset (without abbrev, thus IMO not in POSIX format) is still interpreted by PostgreSQL as POSIX format:

SET TIME ZONE '+02:00';

At least I assume, because checking the UTF offset via:

SELECT EXTRACT(TIMEZONE FROM now()) * interval '1sec';

yields -02:00:00, which means the offset from Greenwich is reversed as noted in docs:


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.

Setting via named time zone yields positive offset from UTC, which is correct:

SET TIME ZONE 'Europe/Prague'; -- +02:00 DST at time of writing
SELECT EXTRACT(TIMEZONE FROM now()) * interval '1sec'; -- `02:00:00`

Postgres docs state there are 3 ways to specify time zone, none of which mention the +00:00 numeric syntax:


PostgreSQL allows you to specify time zones in three different forms:

• A full time zone name, for example America/New_York. The recognized time zone names are listed in the pg_timezone_names view (see Section 51.90). PostgreSQL uses the widely-used IANA time zone data for this purpose, so the same time zone names are also recognized by much other software.

• A time zone abbreviation, for example PST. Such a specification merely defines a particular offset from UTC, in contrast to full time zone names which can imply a set of daylight savings transition-date rules as well. The recognized abbreviations are listed in the pg_timezone_abbrevs view (see Section 51.89). You cannot set the configuration parameters TimeZone or log_timezone to a time zone abbreviation, but you can use abbreviations in date/time input values and with the AT TIME ZONE operator.

• In addition to the timezone names and abbreviations, PostgreSQL will accept POSIX-style time zone specifications of the form STDoffset or STD`offset

The manual can easily be misread there. Quoting the same source, but with one more leading sentence:

All timezone-aware dates and times are stored internally in UTC. They
are converted to local time in the zone specified by the TimeZone
configuration parameter before being displayed to the client.

PostgreSQL allows you to specify time zones in three different forms:

[...]

This paragraph deals with what's allowed in the TimeZone configuration parameter (and, accordingly, the AT TIME ZONE construct).

The part of the manual you quote further down in your question deals with Time Zone Input in timestamp literals which follows the mentioned ISO-8601 convention.

You need this part of the manual explaining what's allowed for SET TIME ZONE (and the AT TIME ZONE construct):

TIME ZONE

SET TIME ZONE value is an alias for SET timezone TOvalue.
The syntax SET TIME ZONE allows special syntax for the time zone
specification. Here are examples of valid values:

'PST8PDT'

The time zone for Berkeley, California.

'Europe/Rome'

The time zone for Italy.

-7

The time zone 7 hours west from UTC (equivalent to PDT). Positive values are east from UTC.

INTERVAL '-08:00' HOUR TO MINUTE

The time zone 8 hours west from UTC (equivalent to PST).

[...]

In your example SET TIME ZONE '+02:00'; looks like an interval, but that's deceiving. Only explicitly declaring INTERVAL makes Postgres treat it as interval. '+02:00' is just another case of STDoffset, with no leading STD, hence defaulting to UTC. So:

SET TIME ZONE '+02:00';

... is equivalent to:

SET TIME ZONE 'UTC+02:00';

These quotes from the first manual page seem instrumental:

The default time zone is specified as a constant numeric offset from UTC.

And:

One should be wary that the POSIX-style time zone feature can lead to
silently accepting bogus input, since there is no check on the
reasonableness of the zone abbreviations. For example, SET TIMEZONE TO FOOBAR0
will work, leaving the system effectively using a rather
peculiar abbreviation for UTC. 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.

But the manual does not seem to mention that the STD part can be missing from the specification. Seems like a documentation bug ...

StackExchange Database Administrators Q#215905, answer score: 3