example datetime timezone utc dst datetimeoffset

example - Las mejores prácticas para guardar la información de datetime y timezone en la base de datos cuando los datos dependen de datetime



timestamp postgresql example (2)

Hubo bastantes preguntas sobre cómo guardar la información de las zonas horarias y la fecha y hora en la base de datos, pero más en el nivel general. Aquí me gustaría abordar un caso específico.

Especificaciones del sistema

  • Tenemos una base de datos del sistema de Pedidos.
  • Es un sistema de múltiples inquilinos en el que los inquilinos pueden usar una zona horaria arbitraria (es arbitrario, pero una única zona horaria por inquilino, guardada en la tabla de inquilinos una vez y nunca cambia)

Regla de negocios necesitaba ser cubierta en DB

  • Cuando el inquilino coloca una orden en el sistema, el número de orden se calcula en función de su fecha y hora local (no es literalmente un número, sino algún tipo de identificador como ORDR-13432-Year-Month-Day ). El cálculo preciso no es importante por el momento, es importante que dependa de la fecha y hora local del inquilino.
  • También queremos poder seleccionar todas las Órdenes, en el nivel del sistema, colocadas entre algunas fechas UTC independientemente del inquilino (para estadísticas / informes generales del sistema)

Nuestra idea inicial

  • Nuestra idea inicial era guardar la fecha y hora UTC en toda la base de datos y, por supuesto, mantener el desplazamiento de la zona horaria de los arrendatarios en relación con la UTC y tener una aplicación que consuma DB siempre convierta las fechas de las fechas en UTC para que la propia DB siempre opere con UTC.

Enfoque 1

  • Guardar la fecha y hora de los arrendatarios locales sería bueno para cada arrendatario, pero luego tenemos problemas con consultas como:

    SELECT * FROM ORDERS WHERE OrderDateTime BETWEEN UTCDateTime1 AND UTCDateTime2

Es problemático porque OrderDateTime en esta consulta significa un momento diferente en el tiempo, según el arrendatario. Por supuesto, esta consulta podría incluir unirse a la tabla de Tenants para obtener un desplazamiento de fecha y hora local que luego calcularía OrderDateTime sobre la marcha para realizar ajustes. ¿Es posible, pero no estoy seguro si es una buena manera de hacerlo?

Enfoque 2

  • Por otro lado, cuando se guarda la fecha y hora UTC, cuando hacemos el cálculo del Número de pedido ya que el día / mes / año en UTC puede diferir de la fecha y hora local

Tomemos ejemplo extremo; digamos que el inquilino está 6 horas por delante de UTC y su fecha y hora local es 2017-01-01 02:00 . UTC sería 2016-12-31 20:00 . El pedido realizado en ese momento debe obtener el Número ''ORDR-13432-2017-1-1'' pero si se guarda UTC se obtendría ORDR-13432-2016-12-31 .

En este caso, en el momento de crear Order in DB, deberíamos obtener datetime UTC, los inquilinos compensan y compilan OrderNumber en base a la hora local calculada de los inquilinos, pero aún guardamos la columna DateTime en UTC.

Preguntas

  1. ¿Cuál es la forma preferida de manejar este tipo de situación?
  2. ¿Existe una buena solución con el ahorro de fechas de UTC porque esa sería muy buena para nosotros debido a los informes a nivel del sistema?
  3. Si va con el ahorro de UTC, ¿es el Enfoque 2) una buena manera de manejar esos casos o hay alguna forma mejor / recomendada?

[ACTUALIZAR]

Basado en los comentarios de Gerard Ashton y Hugo:

La pregunta inicial no estaba clara con respecto al detalle si el arrendatario puede cambiar la zona horaria o no, y qué sucede si la autoridad política cambia las propiedades de la zona horaria o la zona horaria de algún territorio. Por supuesto, eso es de una importancia extrema, pero no está en el centro de esta pregunta. Podríamos abordar eso en una pregunta separada.

Por el bien de esta pregunta, asumamos que el inquilino no cambiará la ubicación. Las propiedades de la zona horaria o la propia zona horaria para esa ubicación pueden cambiar y esos cambios se manejarán en el sistema por separado de esta pregunta.

La respuesta de Hugo es correcta en su mayoría, pero agregaré algunos puntos clave:

  • Cuando almacena la zona horaria del cliente, NO almacene un desplazamiento numérico. Como han señalado otros, el desplazamiento de UTC es solo para un punto en el tiempo, y puede cambiar fácilmente por DST y por otras razones. En su lugar, debe almacenar un identificador de zona horaria, preferiblemente un identificador de zona horaria IANA como una cadena, como "America/Los_Angeles" . Lea más en la etiqueta de zona horaria wiki .

  • Su campo OrderDateTime debe representar absolutamente la hora en UTC. Sin embargo, dependiendo de su plataforma de base de datos, tiene varias opciones para almacenar esto.

    • Por ejemplo, si utiliza Microsoft SQL Server, un buen enfoque es almacenar la hora local en una columna de datetimeoffset , que preserva el desplazamiento de UTC. Tenga en cuenta que cualquier índice que cree en esa columna se basará en el equivalente de UTC, por lo que obtendrá un buen rendimiento de consulta al realizar la consulta de rango.

    • Si utiliza otras plataformas de bases de datos, es posible que desee almacenar el valor UTC en un campo de timestamp . Algunas bases de datos también tienen una timestamp with time zone , pero entiendo que no significa que almacene la zona horaria o el desplazamiento, solo significa que puede hacer conversiones por usted implícitamente a medida que almacena y recupera valores. Si tiene la intención de representar siempre UTC, a menudo, la timestamp (sin zona horaria) o solo la datetime es más apropiada.

  • Dado que cualquiera de los métodos anteriores almacenará una hora UTC, también deberá considerar cómo realizar operaciones que necesitan un índice de valores de hora local. Por ejemplo, es posible que necesite crear un informe diario, en función del día de la zona horaria del usuario. Para eso, necesitarías agrupar por fecha local. Si intenta calcular eso en el momento de la consulta desde su valor UTC, terminará escaneando toda la tabla.

    Un buen enfoque para resolver esto es crear una columna separada para la date local (o quizás incluso la datetime local, dependiendo de sus necesidades, pero no un datetimeoffset y datetimeoffset o datetimeoffset / timestamp ). Esta podría ser una columna completamente aislada que rellene por separado, o podría ser una columna calculada / calculada en base a su otra columna. Use esta columna en un índice para que pueda filtrar o agrupar por fecha local.

  • Si opta por el enfoque de columna computada, necesitará saber cómo convertir entre zonas horarias en la base de datos. Algunas bases de datos tienen una función convert_tz incorporada que entiende los identificadores de zona horaria de IANA.

    Si está utilizando Microsoft SQL Server, puede usar la nueva función AT TIME ZONE en SQL 2016 y Azure SQL DB, pero eso solo funciona con los identificadores de zona horaria de Microsoft. Para usar los identificadores de zona horaria de IANA, necesitará una solución de terceros, como mi proyecto de Soporte de zona horaria de SQL Server .

  • En el momento de la consulta, evite utilizar la instrucción BETWEEN . Es totalmente inclusivo. Funciona bien para fechas enteras, pero cuando tienes tiempo involucrado, es mejor hacer una consulta de rango medio abierto, como por ejemplo:

    ... WHERE OrderDateTime >= @t1 AND OrderDateTime < @t2

    Por ejemplo, si @t1 fuera el comienzo de hoy, @t2 sería el comienzo de mañana.

Con respecto al escenario discutido en los comentarios donde la zona horaria del usuario ha cambiado:

  • Si elige calcular la fecha local en la base de datos, la única situación de la que debe preocuparse es si una ubicación o empresa cambia de zona horaria sin que se produzca una "división de zona". Una división de zona es cuando se introduce un nuevo identificador de zona horaria que cubre el área que cambió, incluidas sus reglas antiguas y nuevas.

    Por ejemplo, la última zona agregada al tzdb de IANA en el momento de escribir esto es America/Punta_Arenas , que fue una zona dividida cuando la parte sur de Chile decidió quedarse en UTC-3 cuando el resto de Chile ( America/Santiago ) Volvió a UTC-4 al final de DST.

    Sin embargo, si una localidad menor en el límite de dos zonas horarias decide cambiar el lado que siguen y no se justifica una división de zonas, entonces es posible que esté utilizando las reglas de su nueva zona horaria en contra de sus datos antiguos.

  • Si almacena la fecha local por separado (calculada en la aplicación, no en la base de datos), no tendrá problemas. El usuario cambia su zona horaria a la nueva, todos los datos antiguos siguen intactos y los nuevos datos se almacenan con la nueva zona horaria.

Recomiendo usar siempre UTC internamente y convertir a una zona horaria solo cuando se muestra la fecha al usuario. Así que tiendo a preferir el enfoque 2.

Si hay una regla de negocios que dice que la fecha / hora local del inquilino debe ser parte del identificador, que así sea. Pero internamente, mantienes la fecha del pedido en UTC.

Usando su ejemplo: un inquilino cuya zona horaria está en UTC+06:00 , por lo que la hora local del inquilino es 2017-01-01 02:00 , que es equivalente a 2016-12-31 20:00 en UTC.

El identificador del pedido sería ORDR-13432-2017-1-1 y la fecha del pedido sería UTC 2016-12-31 20:00Z .

Para obtener todos los pedidos entre 2 fechas, esta consulta es sencilla:

SELECT * FROM ORDERS WHERE OrderDateTime BETWEEN UTCDateTime1 AND UTCDateTime2

Porque OrderDateTime está en UTC.

Si busca un inquilino específico, puede obtener la zona horaria correspondiente, convertir la fecha en consecuencia y buscarla. Usando el mismo ejemplo anterior (la zona horaria del inquilino está en UTC+06:00 ), para obtener todos los pedidos realizados en 2017-01-01 (en la hora local del inquilino):

--get tenant timezone --startUTC=tenant''s local 2017-01-01 00:00 converted to UTC (2016-12-31T18:00Z) --endUTC=tenant''s local 2017-01-01 23:59:59.999 converted to UTC (2017-01-01T17:59:59.999) SELECT * FROM ORDERS WHERE OrderDateTime between startUTC and endUTC

Esto obtendrá ORDR-13432-2017-1-1 correctamente.

Para realizar consultas para varios inquilinos en diferentes zonas horarias, ambos enfoques requieren una unión, por lo que ninguno es "mejor" para este caso.

A menos que cree una columna adicional con la fecha / hora local del inquilino (el UTC OrderDateTime convierte a la zona horaria del inquilino). Será redundante, pero puede ayudarlo con consultas que realizan búsquedas en más de una zona horaria. Si ese es un compromiso razonable, dependerá de la frecuencia con la que se realicen esas consultas.