continuando con nuestra serie de tipos de datos PostgreSQL hoy vamos a introducir los tipos de datos de fecha, marca de tiempo e intervalo.

la implementación de PostgreSQL del calendario es muy buena, y vamos a mostrar algunos ejemplos de ratones sobre lo confuso que es este asunto. El tiempo zonenotion en particular es principalmente una herramienta política en estos días, y hace nosense en un principio de ingeniería: no hay manera de resolver problemas de tiempo zoneproblems de primera mano principios!,

Fecha/Hora y zonas horarias

manejar fechas y horas y zonas horarias es un asunto muy complejo, y en thistopic, puedes leer la pieza de Erik Naggum la larga y dolorosa historia del tiempo.

los capítulos de la documentación de PostgreSQL con los títulos Date/TimeTypes,Data Type FormattingFunctions,y Date / Time Functions and operatorscover todo lo que necesita saber sobre fecha, hora, marcas de tiempo y zonas horarias con PostgreSQL.

la primera pregunta que tenemos que responder aquí es sobre el uso de marcas de tiempo con o sin zonas horarias de nuestras aplicaciones., La respuesta es simple: siempre use marcas de tiempo con zonas horarias.

un mito común es que almacenar zonas horarias sin duda agregará a su espacio de almacenamiento y memoria. En realidad no es el caso:

select pg_column_size(timestamp without time zone 'now'), pg_column_size(timestamp with time zone 'now');

 pg_column_size │ pg_column_size ════════════════╪════════════════ 8 │ 8(1 row)

PostgreSQL utiliza de forma predeterminada bigint internamente para almacenar la hora, y en disco y en memoria de formato son los mismos con o sin tiempo zonesupport., Aquí está toda la definición del tipo en el código fuente de PostgreSQL(en src/include/datatype/timestamp.h):

typedef int64 Timestamp;typedef int64 TimestampTz;

de la documentación de PostgreSQL para marcas de tiempo, así es como funciona:

para la marca de tiempo con zona horaria, el valor almacenado internamente está siempre en UTC(hora coordinada universal, tradicionalmente conocida como hora media de Greenwich,GMT). Un valor de entrada que tiene una zona horaria explícita especificada se convierte a UTC utilizando el desplazamiento apropiado para esa zona horaria., Si no se indica ninguna zona horaria en la cadena de entrada, entonces se asume que está en la zona horaria indicada por el parámetro zona horaria del sistema, y se convierte en UTCusing el desplazamiento para la zona horaria.

PostgreSQL no almacena la zona horaria de la que proviene con su marca de tiempo.En su lugar, se convierte hacia y desde la zona horaria de entrada y salida, como hemos visto para el texto con client_encoding.

en este script, jugamos con la zona horaria del cliente y cambiamos de un valor francés a otro valor francés, ya que Tahití es una isla en el Pacífico que forma parte de Francia., Aquí está la salida completa como se ve al ejecutar este script, cuando se inicia con psql -a -f tz.sql:

primero, vemos que la función now () siempre devuelve la misma marca de tiempo dentro de una sola transacción. Si quieres ver el reloj corriendo mientras estás en atransaction, usa la función clock_timestamp () en su lugar.

entonces, vemos que cuando cambiamos la configuración del cliente de zona horaria, PostgreSQLoutputs timestamps as expected, en la zona horaria seleccionada., Si administra una aplicación con usuarios en diferentes zonas horarias y desea mostrar la hora en su propia zona horaria preferida local, entonces puede establecer la zona horaria en su código de aplicación antes de realizar cualquier procesamiento relacionado con la marca de tiempo, y hacer que PostgreSQL haga todo el trabajo duro por usted.,

finalmente, al seleccionar back de la tabla tstz, vemos que columntstz se da cuenta de que ambos valores insertados en realidad son el mismo punto en el tiempo, pero vistos desde diferentes lugares del mundo, mientras que la columna ts hace imposible comparar las entradas y darse cuenta de que realmente ocurrieron exactamente al mismo tiempo.

como se ha dicho antes, incluso cuando se utilizan marcas de tiempo con zona horaria, PostgreSQL no almacenará la zona horaria en uso en el momento de entrada, por lo que no hay manera desde nuestra tabla TSTZ de saber que las entradas son al mismo tiempo, pero solo de diferentes lugares.,

la apertura de esta sección enlaza con la larga y dolorosa historia del tiempo, y si aún no lo has leído, quizás ahora sea un buen momento. Permítanme citar una parte relevante del artículo aquí:

el problema básico con el tiempo es que necesitamos expresar tanto el tiempo como el lugar cuando queremos colocar algún evento en el tiempo y el espacio, sin embargo, tendemos a asumir coordenadas espaciales incluso más de lo que asumimos coordinaciones temporales, y en el caso del tiempo en la comunicación ordinaria, se omite simplemente por completo., A pesar de la existencia de zonas horarias y regímenes de tiempo de ahorro de luz estrangulada en todo el mundo, la mayoría de las personas son blithelyunaware de su propia zona horaria y ciertamente de cómo se relaciona con las referencias estándar. La mayoría de las personas tampoco saben que al elegir una notación que esté cerca de la expresión hablada o escrita de las fechas, hacen que no signifique nada para las personas que pueden no compartir la cultura, pero aún pueden leer el idioma., Es poco probable que la gente cambie lo suficiente como para dejar estas cuestiones en paz, por lo que las personas responsables de las computadoras necesitan abordar las cuestiones y resistir el impulso abrumador de abreviar y eliminar el contenido.

hay varias opciones disponibles para introducir valores de marca de tiempo en PostgreSQL. Lo más fácil es usar el formato ISO, por lo que si el código de su aplicación lo permite, Ya está todo listo. En el siguiente ejemplo dejamos la zona horaria fuera, asusually, it’s handled by the timezone session parameter, as seen above., Si lo necesita, por supuesto, puede ingresar la zona horaria en los valores de marca de tiempo directamente:

select timestamptz '2017-01-08 04:05:06', timestamptz '2017-01-08 04:05:06+02';

a la hora de insertar o actualizar, use las mismas cadenas literales sin el typedecoration: PostgreSQL ya conoce el tipo de la columna de destino, y lo usa para analice los valores literales en la instrucción DML.

algunos casos de uso de aplicaciones solo necesitan la fecha. A continuación, utilice el tipo de datos date en PostgreSQL., Por supuesto, entonces es posible comparar una fecha y atimestamp con la zona horaria en sus consultas SQL, e incluso agregar un timeoffset encima de su fecha para construir una marca de tiempo.

intervalos de tiempo

PostgreSQL implementa un tipo de datos de intervalo junto con los tipos de datos time, dateand timestamptz.,ración, como amonth o dos semanas, o incluso un milisegundo:

set intervalstyle to postgres;select interval '1 month', interval '2 weeks', 2 * interval '1 week', 78389 * interval '1 ms';

El defecto de PostgreSQL de salida se parece a esto:

 interval │ interval │ ?column? │ ?column? ══════════╪══════════╪══════════╪══════════════ 1 mon │ 14 days │ 14 days │ 00:01:18.389(1 row)

Varios intervalstyle valores son posibles, y el settingpostgres_verbose es bastante agradable interactivo de postgresql sesiones:

set intervalstyle to postgres_verbose;select interval '1 month', interval '2 weeks', 2 * interval '1 week', 78389 * interval '1 ms';

Esta vez llegamos a un usuario-amistoso de salida:

 interval │ interval │ ?column? │ ?column? ══════════╪═══════════╪═══════════╪═════════════════════ @ 1 mon │ @ 14 days │ @ 14 days │ @ 1 min 18.389 secs(1 row)

¿cuánto tiempo dura un mes?, Bueno, depende de qué mes, y PostgreSQL sabe que:

cuando adjuntas un intervalo a una fecha o marca de tiempo en PostgreSQL, entonces el número de días en ese intervalo se ajusta a la entrada de calendario específica que has elegido. De lo contrario, un intervalo de un mes se considera que es de 30 días. Aquí vemos que calcular el último día de febrero es muy fácil:

conclusión

la implementación del calendario de PostgreSQL es muy buena, ¡así que úsala!,

este artículo es un extracto de mi libro Mastering PostgreSQL in ApplicationDevelopment, que enseña a los desarrolladores SQL para que puedan reemplazar miles de líneas de código con consultas muy simples. El libro tiene un capítulo completo sobre los tipos de datos en PostgreSQL, ¡échale un vistazo!