Mejores prácticas para guardar información de fecha y hora y zona horaria en la base de datos cuando los datos dependen de la fecha y hora

Solución:

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

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

• Tu OrderDateTime El campo debe representar absolutamente la hora en UTC. Sin embargo, dependiendo de la plataforma de su base de datos, tiene varias opciones sobre cómo almacenar esto.

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

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

• 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 deba crear un informe diario, basado en el día de la zona horaria del usuario. Para eso, necesitaría agrupar por la fecha local. Si intenta calcular eso en el momento de la consulta a partir de su valor UTC, terminará escaneando toda la tabla.

  Un buen enfoque para lidiar con esto es crear una columna separada para el local. date (o tal vez incluso el local datetime dependiendo de tus necesidades, pero no a datetimeoffset o timestamp). Esta podría ser una columna completamente aislada que debe completar por separado, o podría ser una columna calculada / calculada basada en su otra columna. Utilice esta columna en un índice para poder filtrar o agrupar por fecha local.

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

  Si usa Microsoft SQL Server, puede usar el nuevo AT TIME ZONE funcionan en SQL 2016 y Azure SQL DB, pero eso solo funciona con identificadores de zona horaria de Microsoft. Para usar 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 el BETWEEN declaración. Es totalmente inclusivo. Funciona bien para fechas completas, pero cuando tiene tiempo involucrado, es mejor hacer una consulta de rango medio abierto, como:

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

  Por ejemplo, si @t1 fueron 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, el único escenario del 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 al momento de escribir esto es America/Punta_Arenas, que era 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 del horario de verano.

  Sin embargo, si una localidad menor en el límite de dos zonas horarias decide cambiar el lado que sigue, y no se justifica una división de zona, entonces potencialmente estaría usando las reglas de su nueva zona horaria contra sus datos anteriores.

• 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 datos nuevos se almacenan con la nueva zona horaria.

Recomendaría usar siempre UTC internamente y convertir a una zona horaria solo cuando se muestre la fecha al usuario. Por eso tiendo a preferir el enfoque 2.

Si hay una regla comercial 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 orden identificador sería ORDR-13432-2017-1-1 y el orden fecha 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 que todos los pedidos se realicen 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 conseguirá ORDR-13432-2017-1-1 correctamente.

---

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

A menos que cree una columna adicional con la fecha / hora local del inquilino (la UTC OrderDateTime convertido a la zona horaria del inquilino). Será redundante, pero puede ayudarlo con consultas que busquen en más de una zona horaria. Si esa es una compensación razonable, dependerá de la frecuencia con la que se realicen esas consultas.

---

---

Utiliza Nuestro Buscador