Comment supprimer la partie temporelle d'une valeur datetime (SQL Server)?

Translate

Voici ce que j'utilise:

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

Je pense qu'il peut y avoir une manière meilleure et plus élégante.

Exigences:

  • Il doit être aussi rapide que possible (moins il y a de lancer, mieux c'est).
  • Le résultat final doit être undatetimetype, pas une chaîne.
This question and all comments follow the "Attribution Required."

Toutes les réponses

Translate

SQL Server 2008 et plus

Dans SQL Server 2008 et versions ultérieures, le moyen le plus rapide est bien sûrConvert(date, @date). Cela peut être renvoyé à undatetimeoudatetime2si nécessaire.

Quel est vraiment le meilleur dans SQL Server 2005 et plus ancien?

J'ai vu des affirmations incohérentes sur ce qui est le plus rapide pour tronquer l'heure d'une date dans SQL Server, et certaines personnes ont même dit qu'elles avaient fait des tests, mais mon expérience a été différente. Faisons donc des tests plus stricts et laissons tout le monde avoir le script afin que si je fais des erreurs, les gens peuvent me corriger.

Les conversions flottantes ne sont pas exactes

Premièrement, je resterais loin de la conversiondatetimeàfloat, car il ne convertit pas correctement. Vous pouvez vous en tirer avec précision, mais je pense que c'est une mauvaise idée de l'utiliser car il communique implicitement aux développeurs qu'il s'agit d'une opération sûre etce n'est pas. Regarde:

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

Ce n'est pas quelque chose que nous devrions enseigner aux gens dans notre code ou dans nos exemples en ligne.

De plus, ce n'est même pas le moyen le plus rapide!

Preuve - Test de performance

Si vous souhaitez effectuer vous-même des tests pour voir comment les différentes méthodes s'empilent réellement, vous aurez besoin de ce script de configuration pour exécuter les tests plus bas:

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

Veuillez noter que cela crée une table de 427,57 Mo dans votre base de données et que son exécution prendra environ 15 à 30 minutes. Si votre base de données est petite et définie sur une croissance de 10%, cela prendra plus de temps que si vous aviez d'abord une taille suffisamment grande.

Maintenant, pour le script de test de performance réel. Veuillez noter qu'il est utile de ne pas renvoyer les lignes au client car cela coûte très cher sur 26 millions de lignes et masquerait les différences de performances entre les méthodes.

Résultats de performance

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;

Quelques analyses erratiques

Quelques notes à ce sujet. Tout d'abord, si vous effectuez simplement un GROUP BY ou une comparaison, il n'est pas nécessaire de reconvertir endatetime. Vous pouvez donc économiser du processeur en évitant cela, à moins que vous n'ayez besoin de la valeur finale à des fins d'affichage. Vous pouvez même GROUP BY la valeur non convertie et mettre la conversion uniquement dans la clause 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)

Découvrez également comment les conversions numériques ne prennent qu'un peu plus de temps pour se reconvertir endatetime, mais levarcharla conversion double presque? Cela révèle la partie de l'UC consacrée au calcul de la date dans les requêtes. Certaines parties de l'utilisation du processeur n'impliquent pas le calcul de la date, et cela semble être quelque chose de proche de 19875 ms dans les requêtes ci-dessus. Ensuite, la conversion prend un montant supplémentaire, donc s'il y a deux conversions, ce montant est utilisé environ deux fois.

Un examen plus approfondi révèle que par rapport àConvert(, 112), laConvert(, 101)la requête a des frais de processeur supplémentaires (car elle utilise unvarchar?), car la deuxième conversion endatene coûte pas autant que la conversion initiale envarchar, mais avecConvert(, 112)il est plus proche du même coût de base du processeur de 20000 ms.

Voici ces calculs sur le temps CPU que j'ai utilisé pour l'analyse ci-dessus:

     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
  • rondest le temps CPU pour un aller-retour versdatetime.

  • Célibataireest le temps CPU pour une seule conversion vers le type de données alternatif (celui qui a pour effet secondaire de supprimer la partie temporelle).

  • baseest le calcul de la soustraction desinglela différence entre les deux invocations:single - (round - single). C'est un chiffre approximatif qui suppose la conversion vers et à partir de ce type de données etdatetimeest approximativement le même dans les deux sens. Il semble que cette hypothèse n'est pas parfaite mais est proche car les valeurs sont toutes proches de 20000 ms à une seule exception.

Une autre chose intéressante est que le coût de base est presque égal au simpleConvert(date)(qui doit avoir un coût presque nul, car le serveur peut extraire en interne la partie entière du jour dès les quatre premiers octets dudatetimeType de données).

Conclusion

Donc, à quoi ça ressemble, c'est que la direction uniquevarcharla méthode de conversion prend environ 1,8 μs et le sens uniqueDateDiffLa méthode prend environ 0,18 μs. Je me base sur le temps "CPU de base" le plus conservateur dans mes tests de 18458 ms au total pour 25 920 000 lignes, donc 23218 ms / 25920000 = 0,18 μs. L'amélioration apparente de 10x semble beaucoup, mais elle est franchement assez petite jusqu'à ce que vous ayez affaire à des centaines de milliers de lignes (617k lignes = 1 seconde d'économie).

Même compte tenu de cette petite amélioration absolue, à mon avis, leDateAddla méthode gagne car c'est la meilleure combinaison de performance et de clarté. La réponse qui nécessite un "nombre magique" de0.50000004va mordre quelqu'un un jour (cinq zéros ou six ???), et c'est plus difficile à comprendre.

Notes complémentaires

Quand j'aurai du temps, je vais changer0.50000004à'12:00:00.003'et voyez comment ça marche. Il est converti en le mêmedatetimevaleur et je trouve que c'est beaucoup plus facile à retenir.

Pour les personnes intéressées, les tests ci-dessus ont été exécutés sur un serveur où @@ Version renvoie ce qui suit:

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

La source
Translate

SQL Server 2008 a un nouveauDateType de donnéeset cela simplifie ce problème à:

SELECT CAST(CAST(GETDATE() AS date) AS datetime)
La source
Translate

Itzik Ben-Gan dansCalculs DATETIME, partie 1(SQL Server Magazine, février 2007) présente trois méthodes pour effectuer une telle conversion (du plus lent au plus rapide; la différence entre la deuxième et la troisième méthode est faible):

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)

Votre technique (lancer versflotte) est suggéré par un lecteur dans le numéro d'avril du magazine. Selon lui, il a des performances comparables à celles de la deuxième technique présentée ci-dessus.

La source
Translate

VotreCAST-FLOOR-CASTsemble déjà être la manière optimale, au moins sur MS SQL Server 2005.

Certaines autres solutions que j'ai vues ont une conversion de chaîne, commeSelect Convert(varchar(11), getdate(),101)en eux, ce qui est plus lent d'un facteur 10.

La source
Translate

S'il vous plaît essayez:

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

SQL2005: Je recommande cast au lieu de dateadd. Par exemple,

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

en moyenne autour de 10%plus rapidesur mon ensemble de données, que

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

(et la diffusion en smalldatetime était encore plus rapide)

La source