valor una tutorial tipo rango por obtener manejo hora fuera fechas fecha está ejemplos desde datos dato crear consola cero aprender postgresql timezone timestamp

una - postgresql tutorial



¿Qué tipo de indicación de fecha y hora debo elegir en una base de datos PostgreSQL? (3)

En primer lugar, el manejo del tiempo y la aritmética de PostgreSQL es fantástico y la Opción 3 está bien en el caso general. Sin embargo, es una vista incompleta del tiempo y las zonas horarias, y puede completarse:

  1. Almacene el nombre de la zona horaria de un usuario como una preferencia del usuario (por ejemplo, America/Los_Angeles , no -0700 ).
  2. Haga que los datos de eventos / tiempos del usuario se envíen localmente a su marco de referencia (lo más probable es una compensación de UTC, como -0700 ).
  3. En la aplicación, convierta el tiempo a UTC y almacénelo utilizando una columna TIMESTAMP WITH TIME ZONE .
  4. Solicitudes de tiempo de devolución locales a la zona horaria de un usuario (es decir, convertir de UTC a America/Los_Angeles ).
  5. Establezca la timezone su base de datos en UTC .

Esta opción no siempre funciona porque puede ser difícil obtener la zona horaria de un usuario y, por lo tanto, el asesoramiento de cobertura para usar TIMESTAMP WITH TIME ZONE para aplicaciones livianas. Dicho esto, permítanme explicar algunos aspectos de fondo de esta esta Opción 4 con más detalle.

Al igual que en la Opción 3, el motivo de WITH TIME ZONE se debe a que el momento en que sucedió algo es un momento absoluto en el tiempo. WITHOUT TIME ZONE produce una zona horaria relativa . Nunca, nunca, jamás mezcles TIMESTAMPs absolutos y relativos.

Desde una perspectiva programática y de coherencia, asegúrese de que todos los cálculos se realicen utilizando UTC como la zona horaria. Este no es un requisito de PostgreSQL, pero ayuda al integrarse con otros lenguajes de programación o entornos. Establecer un CHECK en la columna para asegurarse de que la columna write time tenga un offset de zona horaria de 0 es una posición defensiva que evita algunas clases de errores (por ejemplo, un script descarga datos a un archivo y otra cosa ordena los datos de tiempo) usando un tipo léxico). De nuevo, PostgreSQL no necesita esto para hacer cálculos de fecha correctamente o para convertir zonas horarias (es decir, PostgreSQL es muy hábil para convertir tiempos entre dos zonas horarias arbitrarias). Para garantizar que los datos que entran a la base de datos se almacenan con un desplazamiento de cero:

CREATE TABLE my_tbl ( my_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), CHECK(EXTRACT(TIMEZONE FROM my_timestamp) = ''0'') ); test=> SET timezone = ''America/Los_Angeles''; SET test=> INSERT INTO my_tbl (my_timestamp) VALUES (NOW()); ERROR: new row for relation "my_tbl" violates check constraint "my_tbl_my_timestamp_check" test=> SET timezone = ''UTC''; SET test=> INSERT INTO my_tbl (my_timestamp) VALUES (NOW()); INSERT 0 1

No es 100% perfecto, pero proporciona una medida de detección de focos lo suficientemente fuerte como para garantizar que los datos ya estén convertidos a UTC. Hay muchas opiniones sobre cómo hacer esto, pero esta parece ser la mejor práctica según mi experiencia.

Las críticas al manejo de la zona horaria de la base de datos están ampliamente justificadas (hay muchas bases de datos que manejan esto con gran incompetencia), sin embargo, el manejo de marcas de tiempo y zonas horarias de PostgreSQL es bastante impresionante (a pesar de algunas "características" aquí y allá). Por ejemplo, una de esas características:

-- Make sure we''re all working off of the same local time zone test=> SET timezone = ''America/Los_Angeles''; SET test=> SELECT NOW(); now ------------------------------- 2011-05-27 15:47:58.138995-07 (1 row) test=> SELECT NOW() AT TIME ZONE ''UTC''; timezone ---------------------------- 2011-05-27 22:48:02.235541 (1 row)

Tenga en cuenta que AT TIME ZONE ''UTC'' elimina la información de la zona horaria y crea un TIMESTAMP WITHOUT TIME ZONE relativo TIMESTAMP WITHOUT TIME ZONE utilizando el marco de referencia ( UTC ) de su objetivo.

Al convertir de un TIMESTAMP WITHOUT TIME ZONE incompleto TIMESTAMP WITHOUT TIME ZONE a un TIMESTAMP WITH TIME ZONE , la zona horaria perdida se hereda de su conexión:

test=> SET timezone = ''America/Los_Angeles''; SET test=> SELECT EXTRACT(TIMEZONE_HOUR FROM NOW()); date_part ----------- -7 (1 row) test=> SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP WITH TIME ZONE ''2011-05-27 22:48:02.235541''); date_part ----------- -7 (1 row) -- Now change to UTC test=> SET timezone = ''UTC''; SET -- Create an absolute time with timezone offset: test=> SELECT NOW(); now ------------------------------- 2011-05-27 22:48:40.540119+00 (1 row) -- Creates a relative time in a given frame of reference (i.e. no offset) test=> SELECT NOW() AT TIME ZONE ''UTC''; timezone ---------------------------- 2011-05-27 22:48:49.444446 (1 row) test=> SELECT EXTRACT(TIMEZONE_HOUR FROM NOW()); date_part ----------- 0 (1 row) test=> SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP WITH TIME ZONE ''2011-05-27 22:48:02.235541''); date_part ----------- 0 (1 row)

La línea de fondo:

  • almacenar la zona horaria de un usuario como una etiqueta con nombre (por ejemplo, America/Los_Angeles ) y no una compensación de UTC (por ejemplo, -0700 )
  • use UTC para todo a menos que exista una razón de peso para almacenar un desplazamiento distinto de cero
  • tratar todos los tiempos UTC distintos de cero como un error de entrada
  • nunca mezcle y combine marcas de tiempo relativas y absolutas
  • también use UTC como la timezone en la base de datos si es posible

Nota de lenguaje de programación aleatorio: el tipo de datos de datetime y datetime de Python es muy bueno para mantener la distinción entre tiempos absolutos y relativos (aunque frustrante al principio hasta que lo complemente con una biblioteca como PyTZ ).

EDITAR

Permítanme explicar la diferencia entre relativo vs absoluto un poco más.

El tiempo absoluto se usa para grabar un evento. Ejemplos: "El usuario 123 inició sesión" o "las ceremonias de graduación comienzan el 2011-05-28 a las 2 p.m. PST". Independientemente de su zona horaria local, si pudiera teletransportarse a donde ocurrió el evento, podría presenciar el evento. La mayoría de los datos de tiempo en una base de datos son absolutos (y por lo tanto deberían ser TIMESTAMP WITH TIME ZONE , idealmente con un desplazamiento +0 y una etiqueta textual que representa las reglas que gobiernan la zona horaria particular, no un desplazamiento).

Un evento relativo sería registrar o programar el tiempo de algo desde la perspectiva de una zona horaria aún por determinar. Ejemplos: "las puertas de nuestro negocio abren a las 8 a.m. y cierran a las 9 p.m.", "nos reunamos todos los lunes a las 7 a.m. para una reunión de desayuno semanal" o "cada Halloween a las 8 p.m.". En general, el tiempo relativo se usa en una plantilla o fábrica para eventos, y el tiempo absoluto se usa para casi todo lo demás. Hay una rara excepción que vale la pena señalar, que debería ilustrar el valor de los tiempos relativos. Para eventos futuros que están lo suficientemente lejos en el futuro donde podría haber incertidumbre sobre el tiempo absoluto en el que algo podría ocurrir, use una marca de tiempo relativa. Aquí hay un ejemplo del mundo real:

Supongamos que es el año 2004 y necesita programar una entrega el 31 de octubre de 2008 a la 1 PST8PDT . PST8PDT En la costa oeste de EE. UU. (Es decir, America/Los_Angeles / PST8PDT ). Si almacenó eso usando el tiempo absoluto usando ''2008-10-31 21:00:00.000000+00''::TIMESTAMP WITH TIME ZONE , la entrega se hubiera presentado a las 2:00 pm porque el gobierno de los Estados Unidos aprobó la Ley de Política Energética de 2005 que cambió las reglas que rigen el horario de verano. En 2004, cuando se programó la entrega, la fecha 10-31-2008 hubiera sido la Hora estándar del Pacífico ( +8000 ), pero a partir del año 2005 + las bases de datos de la zona horaria reconocieron que el 10-31-2008 habría sido el horario de verano de Pacific Day ( +0700 ). Almacenar una marca de tiempo relativa con la zona horaria hubiera resultado en un cronograma de entrega correcto porque una marca de tiempo relativa es inmune a la manipulación no informada del Congreso. Donde el límite entre usar tiempos relativos vs absolutos para programar cosas es, es una línea difusa, pero mi regla de oro es que la programación para cualquier cosa en el futuro más allá de 3-6mo debería hacer uso de marcas de tiempo relativas (programado = absoluto vs planeado = relativo ???).

El otro / último tipo de tiempo relativo es el INTERVAL . Ejemplo: "la sesión se apagará 20 minutos después de que un usuario inicie sesión". Un INTERVAL se puede utilizar correctamente con marcas de tiempo absolutas ( TIMESTAMP WITH TIME ZONE ) o marcas de tiempo relativas ( TIMESTAMP WITH TIME ZONE TIMESTAMP WITHOUT TIME ZONE ). Es igualmente correcto decir que "una sesión de usuario caduca 20 minutos después de un inicio de sesión exitoso (login_utc + session_duration)" o "nuestra reunión de desayuno matutino solo puede durar 60 minutos (recurring_start_time + meeting_length)".

Los últimos fragmentos de confusión: DATE , TIME , TIME WITHOUT TIME ZONE y TIME WITH TIME ZONE son todos tipos de datos relativos. Por ejemplo: ''2011-05-28''::DATE representa una fecha relativa ya que no tiene información de zona horaria que podría usarse para identificar la medianoche. Del mismo modo, ''23:23:59''::TIME es relativo porque no conoce ni la zona horaria ni la DATE representada por la hora. Incluso con ''23:59:59-07''::TIME WITH TIME ZONE , no sabes cuál será la DATE . Y, por último, DATE con un huso horario no es, de hecho, una DATE , es un TIMESTAMP WITH TIME ZONE :

test=> SET timezone = ''America/Los_Angeles''; SET test=> SELECT ''2011-05-11''::DATE AT TIME ZONE ''UTC''; timezone --------------------- 2011-05-11 07:00:00 (1 row) test=> SET timezone = ''UTC''; SET test=> SELECT ''2011-05-11''::DATE AT TIME ZONE ''UTC''; timezone --------------------- 2011-05-11 00:00:00 (1 row)

Poner fechas y zonas horarias en bases de datos es algo bueno, pero es fácil obtener resultados sutilmente incorrectos. Se requiere un mínimo esfuerzo adicional para almacenar la información de tiempo correcta y completamente, sin embargo, eso no significa que siempre se requiera un esfuerzo extra.

Me gustaría definir una mejor práctica para almacenar marcas de tiempo en mi base de datos de Postgres en el contexto de un proyecto de múltiples zonas horarias.

puedo

  1. elija TIMESTAMP WITHOUT TIME ZONE y recuerde qué zona horaria se utilizó en el momento de la inserción para este campo
  2. elija TIMESTAMP WITHOUT TIME ZONE y agregue otro campo que contendrá el nombre de la zona horaria que se utilizó en el momento de la inserción
  3. elija TIMESTAMP WITH TIME ZONE e inserte las marcas de tiempo en consecuencia

Tengo una ligera preferencia por la opción 3 (marca de tiempo con zona horaria) pero me gustaría tener una opinión educada sobre el tema.

La respuesta de Sean es demasiado compleja y engañosa.

El hecho es que tanto "CON HUSO HORARIO" como "SIN HUSO HORARIO" almacenan el valor como una marca de tiempo UTC absoluta similar a Unix. La diferencia está en cómo se muestra la marca de tiempo. Cuando "CON zona horaria", el valor mostrado es el valor almacenado UTC traducido a la zona del usuario. Cuando "SIN zona horaria" el valor almacenado UTC se tuerce para mostrar la misma cara del reloj sin importar la zona que el usuario haya configurado ".

La única situación en la que se puede utilizar un "SIN zona horaria" es cuando el valor de la cara del reloj es aplicable independientemente de la zona real. Por ejemplo, cuando una marca de tiempo indica cuándo se cerrarán las casillas de votación (es decir, cierran a las 20:00 independientemente de la zona horaria de una persona).

Use la opción 3. Siempre use "WITH time zone" a menos que haya una razón muy específica para no hacerlo.

Mi preferencia es hacia la opción 3, ya que Postgres puede hacer todo el trabajo recalculando las marcas de tiempo relativas a la zona horaria para ti, mientras que con las otras dos tendrás que hacerlo tú mismo. La sobrecarga de almacenamiento adicional de almacenar la marca de tiempo con una zona horaria es realmente insignificante a menos que esté hablando de millones de registros, en cuyo caso, de todos modos, probablemente ya tenga requisitos de almacenamiento sustanciosos.