Time zone names with identical properties yield different result when applied to timestamp

by

Right after I posted this, I ran another query to check on a suspicion:

SELECT * FROM pg_timezone_abbrevs
WHERE  abbrev IN ('CEST', 'CET');

 abbrev | utc_offset | is_dst
--------+------------+--------
 CEST   | 02:00:00   | t
 CET    | 01:00:00   | f

As it turns out, there is also a time zone abbreviation named CET (which makes sense, “CET” being an abbreviation). And it seems that PostgreSQL picks the abbreviation over the full name. So, even though I found CET in the time zone names, the expression ‘2012-01-18 1:0 CET’::timestamptz is interpreted according to the subtly different rules for time zone abbreviations.

SELECT '2012-01-18 1:0 CEST'::timestamptz(0)
      ,'2012-01-18 1:0 CET'::timestamptz(0)
      ,'2012-01-18 1:0 Europe/Vienna'::timestamptz(0);

      timestamptz       |      timestamptz       |      timestamptz
------------------------+------------------------+------------------------
 2012-01-18 00:00:00+01 | 2012-01-18 01:00:00+01 | 2012-01-18 01:00:00+01


SELECT '2012-08-18 1:0 CEST'::timestamptz(0)
      ,'2012-08-18 1:0 CET'::timestamptz(0)
      ,'2012-08-18 1:0 Europe/Vienna'::timestamptz(0);

      timestamptz       |      timestamptz       |      timestamptz
------------------------+------------------------+------------------------
 2012-08-18 01:00:00+02 | 2012-08-18 02:00:00+02 | 2012-08-18 01:00:00+02

I find 10 cases of time zone abbreviations in the time zone names and fail to understand why those are there. What’s the purpose?

Among those, the time offset (utc_offset) disagrees in four cases due to the DST setting:

SELECT n.*, a.*
FROM   pg_timezone_names n 
JOIN   pg_timezone_abbrevs a ON  a.abbrev = n.name
WHERE  n.utc_offset <> a.utc_offset;

 name | abbrev | utc_offset | is_dst | abbrev | utc_offset | is_dst
------+--------+------------+--------+--------+------------+--------
 CET  | CEST   | 02:00:00   | t      | CET    | 01:00:00   | f
 EET  | EEST   | 03:00:00   | t      | EET    | 02:00:00   | f
 MET  | MEST   | 02:00:00   | t      | MET    | 01:00:00   | f
 WET  | WEST   | 01:00:00   | t      | WET    | 00:00:00   | f

In these cases, people may be fooled (like I was), looking up the tz name and finding a time offset that is not actually applied. That is an unfortunate design – if not a bug, at least a documentation bug.

I fail to find anything in the manual about how ambiguities between time zone names and abbreviations are resolved. Obviously abbreviations take precedence.

Appendix B.1. Date/Time Input Interpretation mentions the lookup for time zone abbreviations, but it remains unclear how time zone names are identified and which of them has priority in case of an ambiguous token.

If the token is a text string, match up with possible strings:

Do a binary-search table lookup for the token as a time zone abbreviation.

Well, there is a slight hint in this sentence that abbreviations come first, but nothing definitive. Also, there is a column abbrev in both tables, pg_timezone_namesand pg_timezone_abbrevs …

More Related Contents:

  1. Ignoring time zones altogether in Rails and PostgreSQL
  2. Can’t subtract offset-naive and offset-aware datetimes
  3. Time zone storage in data type “timestamp with time zone”
  4. Difference between timestamps with/without time zone in PostgreSQL
  5. PostgreSQL how to concat interval value ‘2 days’
  6. Different timezone_types on DateTime object
  7. Subtract hours from the now() function
  8. Find if 24 hrs have passed between datetimes
  9. Timezone conversion in php
  10. How to convert a UTC datetime to a local datetime using only standard library?
  11. How to search a specific value in all tables (PostgreSQL)?
  12. Connecting to Postgresql in a docker container from outside
  13. What is the format for the PostgreSQL connection string / URL?
  14. Where does PostgreSQL store the database?
  15. Grant all on a specific schema in the db to a group role in PostgreSQL
  16. Can I make a plpgsql function return an integer without using a variable?
  17. Migrate database from Postgres to MySQL [closed]
  18. What is the difference between UTC and GMT?
  19. Why can I create a table with PRIMARY KEY on a nullable column?
  20. Oracle SQL Developer and PostgreSQL
  21. PostgreSQL array of elements that each are a foreign key
  22. Configure JPA to let PostgreSQL generate the primary key value
  23. How can I send some http request from postgresql function or trigger
  24. How to tell if a timezone observes daylight saving at any time of the year?
  25. PostgreSQL disable more output
  26. PostgreSQL array_agg order
  27. Get UTC offset from time zone name in python
  28. PostgreSQL jsonb, `?` and JDBC
  29. Prevent timezone conversion on deserialization of DateTime value
  30. Error when pushing data to Heroku: time zone displacement out of range

Leave a Comment