¿Cómo eliminar la porción de tiempo de un valor de fecha y hora (SQL Server)?

Esto es lo que uso:

SELECT CAST(FLOOR(CAST(getdate() as FLOAT)) as DATETIME) 

Estoy pensando que puede haber una manera mejor y más elegante.

Requisitos:

  • Tiene que ser lo más rápido posible (cuanto menos casting, mejor).
  • El resultado final debe ser un tipo de datetime y datetime , no una cadena.

SQL Server 2008 y posteriores

En SQL Server 2008 en adelante, por supuesto, la forma más rápida es Convert(date, @date) . Esto puede enviarse a datetime o datetime2 si es necesario.

¿Qué es realmente mejor en SQL Server 2005 y más antiguo?

He visto afirmaciones inconsistentes sobre lo que es más rápido para truncar el tiempo de una fecha en SQL Server, y algunas personas incluso dijeron que lo hicieron, pero mi experiencia ha sido diferente. Así que hagamos algunas pruebas más estrictas y dejemos que todos tengan el guión, así que si cometo errores, la gente puede corregirme.

Las conversiones flotantes no son precisas

Primero, me mantendría alejado de convertir datetime para float , porque no se convierte correctamente. Puede salirse con la suya haciendo la eliminación del tiempo con precisión, pero creo que es una mala idea usarla porque comunica implícitamente a los desarrolladores que esta es una operación segura y no lo es . Echar un vistazo:

 declare @d datetime; set @d = '2010-09-12 00:00:00.003'; select Convert(datetime, Convert(float, @d)); -- result: 2010-09-12 00:00:00.000 -- oops 

Esto no es algo que deberíamos enseñarles a las personas en nuestro código o en nuestros ejemplos en línea.

Además, ¡ni siquiera es la manera más rápida!

Prueba – Prueba de rendimiento

Si quiere realizar algunas pruebas usted mismo para ver cómo se acumulan los diferentes métodos, entonces necesitará este script de instalación para ejecutar las pruebas más abajo:

 create table AllDay (Tm datetime NOT NULL CONSTRAINT PK_AllDay PRIMARY KEY CLUSTERED); declare @d datetime; set @d = DateDiff(Day, 0, GetDate()); insert AllDay select @d; while @@ROWCOUNT != 0 insert AllDay select * from ( select Tm = DateAdd(ms, (select Max(DateDiff(ms, @d, Tm)) from AllDay) + 3, Tm) from AllDay ) X where Tm < DateAdd(Day, 1, @d); exec sp_spaceused AllDay; -- 25,920,000 rows 

Tenga en cuenta que esto crea una tabla de 427.57 MB en su base de datos y tardará entre 15 y 30 minutos en ejecutarse. Si su base de datos es pequeña y está configurada para un 10% de crecimiento, tomará más tiempo que si tuviera el tamaño suficiente.

Ahora para el guión de prueba de rendimiento real. Tenga en cuenta que es útil no devolver las filas al cliente, ya que es caro en 26 millones de filas y podría ocultar las diferencias de rendimiento entre los métodos.

Resultados de rendimiento

 set statistics time on; -- (All queries are the same on io: logical reads 54712) GO declare @dd date, @d datetime, @di int, @df float, @dv varchar(10); -- Round trip back to datetime select @d = CONVERT(date, Tm) from AllDay; -- CPU time = 21234 ms, elapsed time = 22301 ms. select @d = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 23031 ms, elapsed = 24091 ms. select @d = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23782 ms, elapsed = 24818 ms. select @d = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 36891 ms, elapsed = 38414 ms. select @d = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 102984 ms, elapsed = 109897 ms. select @d = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 103390 ms, elapsed = 108236 ms. select @d = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 123375 ms, elapsed = 135179 ms. -- Only to another type but not back select @dd = Tm from AllDay; -- CPU time = 19891 ms, elapsed time = 20937 ms. select @di = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 21453 ms, elapsed = 23079 ms. select @di = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23218 ms, elapsed = 24700 ms select @df = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 29312 ms, elapsed = 31101 ms. select @dv = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 64016 ms, elapsed = 67815 ms. select @dv = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 64297 ms, elapsed = 67987 ms. select @dv = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 65609 ms, elapsed = 68173 ms. GO set statistics time off; 

Algunos análisis engañosos

Algunas notas sobre esto. En primer lugar, si solo realiza un GROUP BY o una comparación, no es necesario convertir de nuevo a datetime . Así que puedes ahorrar algo de CPU evitando eso, a menos que necesites el valor final para mostrarlo. Incluso puede AGRUPAR por el valor no convertido y poner la conversión solo en la cláusula SELECT:

 select Convert(datetime, DateDiff(dd, 0, Tm)) from (select '2010-09-12 00:00:00.003') X (Tm) group by DateDiff(dd, 0, Tm) 

Además, vea cómo las conversiones numéricas solo toman un poco más de tiempo para volver a convertirse en datetime y datetime , pero la conversión varchar casi se duplica. Esto revela la porción de la CPU que está dedicada al cálculo de la fecha en las consultas. Hay partes del uso de la CPU que no implican el cálculo de la fecha, y esto parece ser algo cercano a 19875 ms en las consultas anteriores. Luego, la conversión requiere un monto adicional, por lo que si hay dos conversiones, esa cantidad se utilizará aproximadamente dos veces.

Más examen revela que, en comparación con Convert(, 112) , la consulta Convert(, 101) tiene algún gasto de CPU adicional (ya que utiliza un varchar más largo?), Porque la segunda conversión date no cuesta tanto como la inicial conversión a varchar , pero con Convert(, 112) está más cerca del mismo costo base de CPU de 20000 ms.

Estos son los cálculos sobre el tiempo de CPU que utilicé para el análisis anterior:

  method round single base ----------- ------ ------ ----- date 21324 19891 18458 int 23031 21453 19875 datediff 23782 23218 22654 float 36891 29312 21733 varchar-112 102984 64016 25048 varchar-101 123375 65609 7843 
  • round es el tiempo de CPU para un viaje de ida y vuelta a datetime .

  • single es el tiempo de CPU para una conversión única al tipo de datos alternativo (el que tiene el efecto secundario de eliminar la porción de tiempo).

  • base es el cálculo de restar de una single la diferencia entre las dos invocaciones: single - (round - single) . Es una cifra aproximada que asume que la conversión hacia y desde ese tipo de datos y datetime y datetime es aproximadamente la misma en cualquier dirección. Parece que esta suposición no es perfecta, pero está cerca porque los valores son todos cercanos a 20000 ms con solo una excepción.

Una cosa más interesante es que el costo base es casi igual al método Convert(date) único (que tiene que ser de casi 0 costo, ya que el servidor puede extraer internamente la porción de días enteros de los primeros cuatro bytes de los datos de datetime tipo).

Conclusión

Entonces, lo que parece es que el método de conversión varchar dirección única toma alrededor de 1,8 μs y el método DateDiff dirección DateDiff toma alrededor de 0,18 μs. Estoy basando esto en el tiempo más conservador de "CPU base" en mi prueba de 18458 ms en total para 25,920,000 filas, entonces 23218 ms / 25920000 = 0.18 μs. La aparente mejora de 10x parece mucho, pero francamente es bastante pequeña hasta que se ocupen de cientos de miles de filas (617k filas = 1 segundo de ahorro).

Incluso teniendo en cuenta esta pequeña mejora absoluta, en mi opinión, el método DateAdd gana porque es la mejor combinación de rendimiento y claridad. La respuesta que requiere un "número mágico" de 0.50000004 va a morder a alguien algún día (cinco ceros o seis?), Además es más difícil de entender.

Notas adicionales

Cuando tenga tiempo voy a cambiar 0.50000004 a '12:00:00.003' y veré cómo funciona. Se convierte al mismo valor de datetime y datetime y me resulta mucho más fácil de recordar.

Para los interesados, las pruebas anteriores se ejecutaron en un servidor donde @@ Version devuelve lo siguiente:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 9 de julio de 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition en Windows NT 5.2 (Build 3790: Service Pack 2)

SQL Server 2008 tiene un nuevo tipo de datos de fecha y esto simplifica este problema para:

 SELECT CAST(CAST(GETDATE() AS date) AS datetime) 

Itzik Ben-Gan en DATETIME Calculations, Parte 1 (SQL Server Magazine, febrero de 2007) muestra tres métodos para realizar dicha conversión (del más lento al más rápido , la diferencia entre el segundo y el tercer método es pequeña):

 SELECT CAST(CONVERT(char(8), GETDATE(), 112) AS datetime) SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) SELECT CAST(CAST(GETDATE() - 0.50000004 AS int) AS datetime) 

Su técnica (fundición para flotar ) es sugerida por un lector en la edición de abril de la revista. Según él, tiene un rendimiento comparable al de la segunda técnica presentada anteriormente.

Su CASTFLOORCAST parece ser la forma óptima, al menos en MS SQL Server 2005.

Algunas otras soluciones que he visto tienen una conversión de cadenas, como Select Convert(varchar(11), getdate(),101) en ellas, que es más lento por un factor de 10.

Por favor, inténtalo:

 SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME] 

SQL2005: recomiendo lanzar en lugar de dateadd. Por ejemplo,

 select cast(DATEDIFF(DAY, 0, datetimefield) as datetime) 

un promedio de alrededor de 10% más rápido en mi conjunto de datos, que

 select DATEADD(DAY, DATEDIFF(DAY, 0, datetimefield), 0) 

(y el casting en smalldatetime fue aún más rápido)

    Intereting Posts