1

If you execute something like the following query on Postgres:

SELECT * FROM pg_timezone_names() WHERE name LIKE '%Rome%';

you get back a bunch of "weird" timezones:

postgres=# SELECT * FROM pg_timezone_names() WHERE name LIKE '%Rome%';
       name        | abbrev | utc_offset | is_dst
-------------------+--------+------------+--------
 Europe/Rome       | CET    | 01:00:00   | f
 posix/Europe/Rome | CET    | 01:00:00   | f
(2 rows)

I'm wondering why posix/ exists, what they stand for (they are not IANA official timezones right?) and when they are used. They look like a blend of POSIX standard and Olson DB, but posix should have -1 as utc_offset, right? I'm considering using the pg_timezone_names() function for giving the users the chance of choosing their own timezone, but I can't understand the meaning of this specific type of timezone in Postgres.

  • it seems to be CET timezones – McNets Jan 29 '17 at 19:24
  • you can change time zone by session or on postgres conf: stackoverflow.com/questions/6663765/postgres-default-timezone – McNets Jan 29 '17 at 19:36
  • it's CET indeed as the abbrev column says, but I want the users to save their timezone preferences. For that reason I want to give them a list of available timezones. But those ones that look like "posix/Europe/Rome" do not make much sense to me! So, I'm wondering why they place those timezone items in postgres and what are their real meaning – Bertuz Jan 29 '17 at 19:42
1

Generally, you shouldn't be using the Posix/ or Etc/ timezones if they're on your system. This isn't a PostgreSQL thing, it's your distributions libc database (sometimes packaged as tzdata/zoneinfo) for the internal timezone functions. Most of that POSIX stuff is nasty and old. PostgreSQL has a document on it which mentions it.

I guess to answer the question specifically,

Two different versions are provided: - The "posix" version is based on the Coordinated Universal Time (UTC). - The "right" version is based on the International Atomic Time (TAI), and it includes the leap seconds.

There really is no reason that I know of not to use the IANA names.

| improve this answer | |
0

In addition to Evan Carroll answer, you can get a list by using something similar to this:

SELECT * FROM pg_timezone_names()
WHERE name LIKE 'Europe%' 
      AND abbrev = 'CET'
ORDER BY name
;

The result is:

+-------------------+--------+------------+--------+
|        name       | abbrev | utc_offset | is_dst |
+-------------------+--------+------------+--------+
| Europe/Amsterdam  | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Andorra    | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Belgrade   | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Berlin     | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Bratislava | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Brussels   | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Budapest   | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Busingen   | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Copenhagen | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Gibraltar  | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Ljubljana  | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Luxembourg | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Madrid     | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Malta      | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Monaco     | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Oslo       | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Paris      | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Podgorica  | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Prague     | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Rome       | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/San_Marino | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Sarajevo   | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Skopje     | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Stockholm  | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Tirane     | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Vaduz      | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Vatican    | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Vienna     | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Warsaw     | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Zagreb     | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| Europe/Zurich     | CET    | 01:00:00   | False  |
+-------------------+--------+------------+--------+
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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