sintaxis - tipo time en postgresql

¿Cuál es un tipo de datos apropiado para almacenar una zona horaria?

Question

Estoy pensando simplemente en usar una cadena en el formato "+ hh: mm" (o "-hh: mm"). ¿Es esto necesario y suficiente?

Nota: No necesito almacenar la fecha o la hora, solo la zona horaria.

Answer 1

"+ hh: mm" y "-hh: mm" no son zonas horarias, son compensaciones UTC. Un buen formato para guardarlos es como un entero con signo con el desplazamiento en minutos. También puede usar cosas como el interval pero eso solo lo ayudará si desea hacer cálculos de fecha directamente en PostgreSQL, como en una consulta, etc. Generalmente, aunque haga estos cálculos en otro idioma, y ​​luego depende de ese idioma si soporta bien el tipo de interval y tiene una buena biblioteca de fecha / hora o no. Pero convertir un entero en una especie de tipo de interval , como Pythons timedelta debería ser trivial, así que personalmente lo almacenaría como un entero.

Las zonas horarias tienen nombres, y aunque no hay nombres estandarizados para las zonas horarias, hay un estándar de facto en la base de datos "tz" o "zoneinfo", y son nombres como "Europa / París", "Américas / Nueva_York" o " Estados Unidos / Pacífico ". Esos deben ser almacenados como cadenas.

Windows usa nombres completamente diferentes, como "Tiempo de romance" (no preguntar). Puede almacenarlos al igual que cadenas, pero lo evitaría, estos nombres no se usan fuera de Windows y los nombres no tienen sentido. Además, las versiones traducidas de Windows tienden a usar nombres traducidos para estas zonas horarias, haciéndolo aún peor.

Las abreviaturas como "PDT" y "EST" no se pueden usar como nombres de zona horaria, porque no son únicas. Hay cuatro (creo, o ¿eran cinco?) Zonas horarias diferentes, todas llamadas "CST", por lo que no es utilizable.

En resumen: para las zonas horarias, almacene el nombre como una cadena. Para las compensaciones UTC, almacene el desplazamiento en minutos como un entero con signo.

Answer 2

En un mundo ideal, podría tener una clave externa para un conjunto de zonas horarias conocidas. Puedes hacer algo parecido a esto con vistas y dominios.

Este consejo de wiki de David E. Wheleer crea un dominio que se prueba para determinar su validez como zona horaria:

CREATE OR REPLACE FUNCTION is_timezone( tz TEXT ) RETURNS BOOLEAN as $$
BEGIN
 PERFORM now() AT TIME ZONE tz;
 RETURN TRUE;
EXCEPTION WHEN invalid_parameter_value THEN
 RETURN FALSE;
END;
$$ language plpgsql STABLE;

CREATE DOMAIN timezone AS CITEXT
CHECK ( is_timezone( value ) );

Es útil tener una lista de zonas horarias conocidas, en cuyo caso podría prescindir del dominio y simplemente imponer la restricción en la tabla que contiene los nombres de zonas horarias conocidos (obtenidos de la vista pg_timezone_names ), evitando la necesidad de exponer el dominio en otra parte:

CREATE TABLE tzone
(
  tzone_name text PRIMARY KEY (tzone_name) CHECK (is_timezone(tzone_name))
);

INSERT INTO tzone (tzone_name)
SELECT name FROM pg_timezone_names;

A continuación, puede hacer cumplir la corrección a través de claves externas:

CREATE TABLE myTable (
...
tzone TEXT REFERENCES tzone(tzone_name)
);

Answer 3

tal vez intervalo

postgres=# select interval ''01:30''; interval ---------- 01:30:00 (1 row) postgres=# select interval ''-01:30''; interval ----------- -01:30:00 (1 row)

Answer 4

Desafortunadamente, PostgreSQL no ofrece un tipo de datos de zona horaria, por lo que probablemente deberías usar text .

interval parece una opción lógica a primera vista, y es apropiado para algunos usos. Sin embargo, no tiene en cuenta el horario de verano, ni considera el hecho de que diferentes regiones en el mismo desplazamiento UTC tienen reglas de horario de verano diferentes.

No hay una asignación 1: 1 desde el desplazamiento UTC hasta la zona horaria.

Por ejemplo, la zona horaria de Australia/Sydney (Nueva Gales del Sur) es UTC+10 ( EST ), o UTC+11 ( EDT ) durante el horario de verano. Sí, es el mismo acrónimo EST que usa Estados Unidos; los acrónimos de zona horaria no son únicos en la base de datos tzdata, por lo que Pg tiene la configuración de abreviaciones de timezone_abbreviations . Peor aún, Brisbane (Queensland) tiene casi la misma longevidad y está en UTC+10 EST ... pero no tiene horario de verano, por lo que en algún momento se encuentra en una compensación de -1 a Nueva Gales del Sur durante el horario de verano de Nueva Gales del Sur.

( Actualización : Más recientemente, Australia adoptó un prefijo A , por lo que utiliza AEST como el acrónimo TZ de los estados del este, pero EST y WST siguen siendo de uso común).

¿Confundir mucho?

Si todo lo que necesita almacenar es un desplazamiento UTC, entonces es apropiado un interval . Si desea almacenar una zona horaria , guárdela como text . Es un dolor validar y convertir a una zona horaria desplazada en este momento, pero al menos hace frente al horario de verano.