without - time zone postgresql

Diferencia entre marcas de tiempo con/sin huso horario en PostgreSQL

Question

¿Los valores de marca de tiempo se almacenan de manera diferente en PostgreSQL cuando el tipo de datos es WITH TIME ZONE versus WITHOUT TIME ZONE ? ¿Pueden ilustrarse las diferencias con casos de prueba simples?

Answer 1

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 tienes una zona horaria base, no se convertirá correctamente.

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

Answer 2

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

Ninguna de las variantes TIMESTAMP almacena una zona horaria (o una compensación), a pesar de lo que sugieren 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 almacena la fecha y hora local (también conocido como fecha del calendario de pared y hora del reloj de pared). Su zona horaria no está especificada en lo que PostgreSQL puede decir (aunque su aplicación puede saber de qué se trata). Por lo tanto, PostgreSQL no hace ninguna conversión relacionada con la zona horaria en entrada o salida. Si el valor se ingresó en la base de datos como ''2011-07-01 06:30:30'' , entonces no ''2011-07-01 06:30:30'' en qué zona horaria lo muestre más tarde, todavía se indicará el año 2011, el mes 07, el día 01, 06 horas, 30 minutos y 30 segundos (en algún formato). Además, PostgreSQL omite 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 los mismos como solo ''2011-07-01 06:30:30'' . Para desarrolladores de Java: es análogo a java.time.LocalDateTime .

• TIMESTAMP WITH TIME ZONE almacena un punto en la línea de tiempo UTC. Cómo se ve (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 un desplazamiento explícito o zona horaria (como ''2011-07-01 06:30:30'' ) se supone que está en el huso horario actual de PostgreSQL sesión, de lo contrario se utiliza el desplazamiento explícitamente especificado o la zona horaria (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 menor resolución), pero con JDBC y JPA 2.2 se supone que java.time.OffsetDateTime a java.time.OffsetDateTime (oa java.util.Date o java.sql.Timestamp de java.sql.Timestamp por supuesto).

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

Answer 3

Las diferencias están cubiertas en la documentación de PostgreSQL para tipos de fecha / hora . Sí, el tratamiento de TIME o TIMESTAMP difiere entre uno WITH TIME ZONE o WITH TIME ZONE WITHOUT TIME ZONE . No afecta cómo se almacenan los valores; afecta cómo 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 razonablemente saber 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 dependiendo 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 particular.

Aquí hay 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)

Answer 4

SELECT MAX(''2017-07-06 12:20:48.446+00'') - MIN(''2017-06-06 12:20:48.446+00'')
as time_to_take from table_name