Datetime vs. Datetime2 de SQL Server

Hay muchos formatos de fecha y hora en SQL Server con diferentes rangos, precisiones, tamaños de almacenamiento y segundas precisiones fraccionarias definidas por el usuario.

A continuación se muestra un breve resumen:

Queremos centrarnos en la comparación de formato datetime y datetime2. En mi empresa actual me encuentro con muchas tablas heredadas que usan datetime. Datetime2 se introdujo por primera vez en SQL Server 2008. Sin embargo, creo que algunos desarrolladores simplemente no conocen las ventajas y desventajas de la hora de date2.

Datetime

Primero hablemos un poco de datetime. Como puede ver arriba, necesita 8 bytes de almacenamiento y tiene un rango de 1753-01-01 a 9999-12-31. Notablemente, tiene un corto alcance hacia atrás. Esto se debe a que Gran Bretaña pasó del calendario juliano al gregoriano en 1752 saltándose unos días. Para ser más precisos, el 2 de septiembre de 1752 fue seguido por el 14 de septiembre de 1752. Debido a que una fecha anterior a 1753 sería ambigua, el tipo datetime no es válido antes de 1753. Otra propiedad bastante notable del tipo de datos datetime es la precisión de 0,00333 segundos, que de hecho es 1/300 de segundo.

Esto parece un poco extraño. No tenemos una precisión de milisegundos con datetime. Vale, ¿pero por qué? Analicemos el tipo de datos datetime en profundidad. En una fecha y hora usamos 4 bytes para la fecha y 4 bytes para la hora. ¿Cómo funciona eso exactamente? Echemos un vistazo.

DECLARE @test DATETIME = '2015-11-29 10:00:00.000';SELECT CAST(@test as varbinary(8))> 0x0000A55F00A4CB80

Así que 0x0000A55F00A4CB80 es hexadecimal. Separémonos los 8 bytes en dos piezas. Primero la cita. 0x0000A55F representa la fecha. En decimal es 42335. Esa es la cantidad de días transcurridos desde 1900-01-01. Prueba:

SELECT DATEADD(DD,42335,'1900-01-01')> 2015-11-29

Ahora, para el momento en que tenemos los últimos 4 bytes 0xA4CB80 traducidos al decimal, es 10800000. Eso significa 10800000 garrapatas a partir de la medianoche. ¿Recuerdas que dije que la precisión es de 1/300 de segundo? Esto se debe al hecho de que datetime almacena la hora en ticks. Así que 10800000 ticks desde medianoche significa 10800000 veces 1/300 de segundo. Calculemos un poco.

Así que tenemos exactamente 10 horas desde la medianoche y eso se traduce perfectamente a las 10:00: 00. Combinado con la fecha que tenemos 2015-11-29 10: 00: 00.

Recuerde que datetime utiliza siempre 8 bytes de almacenamiento y también tenga en cuenta que los primeros cuatro bytes que representan la fecha pueden ser negativos (2complement) ya que la fecha puede ser anterior a 1900. Por ejemplo, en 1890-11-29 obtienes los primeros 4 bytes como 0xFFFFF308, que se traduce como complemento de 2 bits de 32 bits a -3320. Y 3320 restar de 1900-01-01 es exactamente 1890-11-29.

datetime2

Todos los tipos de datos de fecha y hora introducidos con SQL Server 2008 tienen un tipo de almacenamiento completamente nuevo que examinaremos ahora. El tipo de datos datetime2 utiliza de 6 a 8 bytes dependiendo de la precisión de milisegundos.

DECLARE @test DATETIME2(3) = '2015-11-29 10:00:00.000';SELECT CAST(@test as varbinary(8))> 0x0300512502BA3A0B

Esta vez se vuelve un poco más complicado. En todos los nuevos tipos de datos datetime, los últimos tres bytes representan la fecha. Esto se debe a un cambio de orden de bytes. Por lo tanto, datetime se almacena como little endian, lo que significa que el byte más significativo está en el extremo izquierdo, mientras que en big endian el byte más significativo se almacena en la posición más a la derecha.

Eso significa que cuando tomamos 0x0300512502BA3A0B la fecha no es 0xBA3A0B sino 0x0B3ABA, ya que un byte es de 2 dígitos hexadecimales.

De nuevo con la matemática: 0x0B3ABA representa el decimal 735930. Esta es exactamente la fecha que queríamos:

SELECT DATEADD(DD,735930,CAST('0001-01-01' as date))> 2015-11-29

Ahora que los bytes se convierten, podemos tomar los últimos bytes de la representación little endian que es 0x0225510003. Tenga en cuenta que el último byte en little endian (que es el primer byte en big endian original) es la precisión indicada. Como puede ver, definimos datetime2 (3), lo que significa que nuestro último byte es 0x03.

Haciendo las matemáticas: 0x02255100 está en decimal 36000000. Dado que usamos precisión 3, que significa precisión de 3 dígitos, calculamos los segundos primero dividiendo nuestro número con 10 a la potencia de precisión que es en nuestro caso 103.

Esto también se traduce perfectamente en 10 horas 0 minutos 0 segundos tal como se indica.

datetime vs datetime2

Finalmente, una comparación simple y simple entre esos dos tipos de datos.

datetime datetime2
max precisa impar precisión de 1/300 100 nanosegundos de precisión
definido por el usuario de precisión no sí que van de 0 a 7
espacio de almacenamiento siempre de 8 bytes 6 – 8 bytes dependiendo de la precisión
utilizable con + o – operador no, utilizar datediff, dateadd etc.
Compatible con el estándar SQL no

Por lo tanto, en general, puede ver que datetime utiliza potencialmente más almacenamiento, tiene una precisión más baja y extraña, tiene un rango más bajo y no es compatible con el estándar SQL, lo que hace que su código se comporte de manera diferente en diferentes DBMS. Por lo tanto, si su aplicación admite date, datetime2 y datetimeoffset, le aconsejo encarecidamente que use los nuevos tipos de datos datetime, ya que apenas tienen desventajas.

Gracias por su tiempo. Para artículos más interesantes, visite http://www.dirtyread.de/

Deja una respuesta

Tu dirección de correo electrónico no será publicada.