Diferencia entre marcas de tiempo con / sin zona horaria en PostgreSQL

Solución:

Las diferencias se tratan en la documentación de PostgreSQL para los tipos de fecha / hora. Si, el tratamiento de TIME o TIMESTAMP difiere entre uno WITH TIME ZONE o WITHOUT TIME ZONE. No afecta la forma en que se almacenan los valores; afecta la forma en que se interpretan.

Los efectos de las zonas horarias en estos tipos de datos se tratan específicamente en los documentos. La diferencia surge de lo que el sistema puede saber razonablemente sobre el valor:

• Con una zona horaria como parte del valor, el valor se puede representar como una hora local en el cliente.

• Sin una zona horaria como parte del valor, la zona horaria predeterminada obvia es UTC, por lo que se representa para esa zona horaria.

El comportamiento difiere en función de al menos tres factores:

• La configuración de la zona horaria en el cliente.
• El tipo de datos (es decir WITH TIME ZONE o WITHOUT TIME ZONE) del valor.
• Si el valor se especifica con una zona horaria en particular.

A continuación, se muestran ejemplos que cubren las combinaciones de esos factores:

foo=> SET TIMEZONE TO 'Japan';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+09
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 06:00:00+09
(1 row)

foo=> SET TIMEZONE TO 'Australia/Melbourne';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+11
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 08:00:00+11
(1 row)

Intento explicarlo de manera más comprensible que la documentación de PostgreSQL referida.

Ninguno TIMESTAMP las variantes almacenan una zona horaria (o un desplazamiento), a pesar de lo que sugieran los nombres. La diferencia está en la interpretación de los datos almacenados (y en la aplicación prevista), no en el formato de almacenamiento en sí:

• TIMESTAMP WITHOUT TIME ZONE historias local fecha-hora (también conocida como fecha del calendario de pared y hora del reloj de pared). Su zona horaria no está especificada por lo que PostgreSQL puede decir (aunque su aplicación puede saber cuál es). Por lo tanto, PostgreSQL no realiza ninguna conversión relacionada con la zona horaria en la entrada o salida. Si el valor se ingresó en la base de datos como '2011-07-01 06:30:30', no importa en qué zona horaria lo muestre más tarde, seguirá diciendo año 2011, mes 07, día 01, 06 horas, 30 minutos y 30 segundos (en algún formato). Además, PostgreSQL ignora cualquier desplazamiento o zona horaria que especifique en la entrada, por lo que '2011-07-01 06:30:30+00' y '2011-07-01 06:30:30+05' son lo mismo que solo '2011-07-01 06:30:30'. Para los desarrolladores de Java: es análogo a java.time.LocalDateTime.

• TIMESTAMP WITH TIME ZONE almacena un punto en la línea de tiempo UTC. La apariencia (cuántas horas, minutos, etc.) depende de su zona horaria, pero siempre se refiere al mismo instante “físico” (como el momento de un evento físico real). La entrada se convierte internamente a UTC y así es como se almacena. Para eso, se debe conocer el desplazamiento de la entrada, por lo que cuando la entrada no contiene ningún desplazamiento explícito o zona horaria (como '2011-07-01 06:30:30') se supone que está en la zona horaria actual de la sesión de PostgreSQL; de lo contrario, se utiliza el desplazamiento o la zona horaria especificada explícitamente (como en '2011-07-01 06:30:30+05'). La salida se muestra convertida a la zona horaria actual de la sesión de PostgreSQL. Para los desarrolladores de Java: es análogo a java.time.Instant (aunque con una resolución más baja), pero con JDBC y JPA 2.2 se supone que debe asignarlo a java.time.OffsetDateTime (o para java.util.Date o java.sql.Timestamp por supuesto).

Algunos dicen que ambos TIMESTAMP las variaciones almacenan la fecha y hora UTC. Algo así, pero es confuso decirlo de esa manera en mi opinión. TIMESTAMP WITHOUT TIME ZONE se almacena como un TIMESTAMP WITH TIME ZONE, que se representa con la zona horaria UTC da el mismo año, mes, día, horas, minutos, segundos y microsegundos que en la fecha y hora local. Pero no está destinado a representar el punto en la línea de tiempo que dice la interpretación UTC, es solo la forma en que se codifican los campos de fecha y hora locales. (Es un grupo de puntos en la línea de tiempo, ya que la zona de tiempo real no es UTC; no sabemos qué es).

Aquí hay un ejemplo que debería ayudar. Si tiene una marca de tiempo con una zona horaria, puede convertir esa marca de tiempo en cualquier otra zona horaria. Si no tiene una zona horaria base, no se convertirá correctamente.

SELECT now(),
   now()::timestamp,
   now() AT TIME ZONE 'CST',
   now()::timestamp AT TIME ZONE 'CST'

Producción:

-[ RECORD 1 ]---------------------------
now      | 2018-09-15 17:01:36.399357+03
now      | 2018-09-15 17:01:36.399357
timezone | 2018-09-15 08:01:36.399357
timezone | 2018-09-16 02:01:36.399357+03

---

---

Utiliza Nuestro Buscador