Los nombres de zonas horarias con propiedades idénticas producen resultados diferentes cuando se aplican a la marca de tiempo

Acabo de pasar una hora en la desesperación con la discrepancia en estos resultados de estas dos expresiones:

db=# SELECT '2012-01-18 1:0 CET '::timestamptz AT TIME ZONE 'UTC' ,'2012-01-18 1:0 Europe/Vienna '::timestamptz AT TIME ZONE 'UTC'; timezone | timezone ---------------------+--------------------- 2012-08- 18 00:00:00 | 2012-08- 17 23:00:00 

Obviamente, la segunda expresión deduce dos horas según las reglas de horario de verano, donde la primera solo utiliza el desplazamiento estándar.

Revisé los catálogos para estos dos nombres de zona horaria. Ambos están allí y tienen el mismo aspecto:

 db=# SELECT * FROM pg_timezone_names WHERE name IN ('CET', 'Europe/Vienna'); name | abbrev | utc_offset | is_dst ---------------+--------+------------+-------- Europe/Vienna | CEST | 02:00:00 | t CET | CEST | 02:00:00 | t 

Consulté el manual de PostgreSQL sobre husos horarios :

PostgreSQL le permite especificar zonas horarias en tres formas diferentes:

Un nombre de zona horaria completa, por ejemplo America / New_York. Los nombres de zona horaria reconocidos se enumeran en la vista pg_timezone_names (consulte la Sección 45.67). PostgreSQL utiliza los datos ampliamente utilizados de la zona horaria zoneinfo para este fin, por lo que los mismos nombres también son reconocidos por muchos otros software.

Una abreviatura de zona horaria, por ejemplo PST. Dicha especificación simplemente define una compensación particular de UTC, a diferencia de los nombres de zona horaria completa que también pueden implicar un conjunto de reglas de fecha de transición de ahorro de luz solar. Las abreviaturas reconocidas se enumeran en la vista pg_timezone_abbrevs (consulte la Sección 45.66). No puede establecer los parámetros de configuración timezone o log_timezone en una abreviatura de zona horaria, pero puede usar abreviaturas en valores de entrada de fecha / hora y con el operador AT TIME ZONE.

Bold Énfasis mío.

Entonces, ¿por qué la diferencia?

Mi configuración (más detalles agregados)

  • PostgreSQL 9.1.4 en Debian Squeeze (estándar squeeze-backports de http://backports.debian.org/debian-backports )

  • La configuración de la timezone local se establece de manera predeterminada en la configuración regional del sistema de_AT.UTF-8 , pero debe ser irrelevante para el ejemplo.

 SELECT version(); version ------------------------------------------------------------------------------------------------------- PostgreSQL 9.1.4 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit SHOW timezone_abbreviations; timezone_abbreviations ------------------------ Default 

… que (supongo) carga abreviaturas de este archivo: /usr/share/postgresql/9.1/timezonesets/Default

Estoy perdido en el origen del nombre de la zona horaria CET . Pero obviamente está ahí en mis instalaciones. Una prueba rápida en sqlfiddle muestra el mismo resultado.

Probé en dos servidores diferentes con una configuración similar. También con PostgreSQL 8.4. Se encontró ‘CET’ como nombre de zona horaria en pg_timezone_names en todos ellos.

Justo después de publicar esto, ejecuté otra consulta para verificar una sospecha:

 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 

Como resultado, también hay una abreviatura de zona horaria llamada CET (que tiene sentido, “CET” es una abreviación). Y parece que PostgreSQL elige la abreviatura del nombre completo. Entonces, aunque encontré CET en los nombres de zona horaria, la expresión ‘2012-01-18 1: 0 CET’ :: timestamptz se interpreta de acuerdo con reglas sutilmente diferentes para las abreviaturas de zona horaria.

 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 

Encuentro 10 casos de abreviaturas de zona horaria en los nombres de zona horaria y no entiendo por qué están allí. ¿Cuál es el propósito?

Entre ellos, el desplazamiento de tiempo ( utc_offset ) no utc_offset en cuatro casos debido a la configuración de horario de verano:

 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 

En estos casos, se puede engañar a la gente (como yo), buscar el nombre tz y encontrar una compensación de tiempo que no se aplica realmente. Ese es un diseño desafortunado, si no un error, al menos un error de documentación .

No encuentro nada en el manual sobre cómo se resuelven las ambigüedades entre los nombres de las zonas horarias y las abreviaturas . Obviamente, las abreviaturas tienen prioridad.

Apéndice B.1. La Interpretación de entrada de fecha / hora menciona la búsqueda de abreviaturas de zona horaria, pero no está claro cómo se identifican los nombres de zona horaria y cuál de ellos tiene prioridad en caso de un token ambiguo.

Si el token es una cadena de texto, haga coincidir las posibles cadenas:

Realice una búsqueda de tabla de búsqueda binaria para el token como abreviación de zona horaria.

Bueno, hay una ligera sugerencia en esta oración de que las abreviaturas son lo primero, pero nada definitivo. Además, hay una columna abbrev en ambas tablas, pg_timezone_names y pg_timezone_abbrevs

La razón por la que las abreviaturas de zona horaria no incluyen las reglas de transición de horario de verano (DST) es que tienden a implicar un estado. Aquí en el medio oeste de los EE. UU., Estamos en CST (Hora estándar del centro) durante los meses de invierno y en CDT (Hora de verano central) el rest del año. Hay áreas anómalas que no usan DST, por lo que se complica.

PostgreSQL no mantiene sus propios datos de zona horaria, aunque empaqueta los últimos datos de zona horaria de Olson en cada versión para los sistemas operativos que no lo proporcionan. En general, PostgreSQL utilizará la información de zona horaria del sistema operativo, por lo que si tiene problemas, asegúrese de tener allí la última versión.

Como referencia, en mi sistema hoy, obtuve estos resultados:

 test = # SELECCIONAR '2012-01-18 1: 0 CET' :: timestamptz AT TIME ZONE 'UTC'
 test- #, '2012-01-18 1: 0 Europe / Vienna' :: timestamptz AT TIME ZONE 'UTC';
       timezone |  zona horaria       
 --------------------- + ---------------------
  2012-01-18 00:00:00 |  2012-01-18 00:00:00
 (1 fila)

 test = # SELECT * FROM pg_timezone_names DONDE nombre IN ('CET', 'Europe / Vienna');
      nombre |  abreviatura |  utc_offset |  is_dst 
 --------------- + -------- + ------------ + --------
  CET |  CEST |  02:00:00 |  t
  Europa / Viena |  CEST |  02:00:00 |  t
 (2 filas)
 test = # SELECT * FROM pg_timezone_abbrevs
 test- # WHERE abreviatura IN ('CEST', 'CET');
  abreviatura |  utc_offset |  is_dst 
 -------- + ------------ + --------
  CEST |  02:00:00 |  t
  CET |  01:00:00 |  F
 (2 filas)

 test = # SELECT '2012-01-18 1: 0 CEST' :: timestamptz (0)
 test- #, '2012-01-18 1: 0 CET' :: timestamptz (0)
 test- #, '2012-01-18 1: 0 Europa / Viena' :: timestamptz (0);
       timestamptz |  timestamptz |  timestamptz       
 ------------------------ + ------------------------ + ------------------------
  2012-01-17 17: 00: 00-06 |  2012-01-17 18: 00: 00-06 |  2012-01-17 18: 00: 00-06
 (1 fila)

 test = # SELECCIONE '2012-08-18 1: 0 CEST' :: timestamptz (0)
 test- #, '2012-08-18 1: 0 CET' :: timestamptz (0)
 test- #, '2012-08-18 1: 0 Europa / Viena' :: timestamptz (0);
       timestamptz |  timestamptz |  timestamptz       
 ------------------------ + ------------------------ + ------------------------
  2012-08-17 18: 00: 00-05 |  2012-08-17 19: 00: 00-05 |  2012-08-17 18: 00: 00-05
 (1 fila)