now DateTime2 vs DateTime dans SQL Server




sql server datetime timezone (12)

Laquelle:

est la méthode recommandée pour stocker la date et l'heure dans SQL Server 2008+?

Je suis conscient des différences de précision (et de l'espace de stockage probablement), mais en ignorant ceux pour l'instant, existe-t-il un document de meilleure pratique sur quand utiliser quoi, ou peut-être devrions-nous simplement utiliser datetime2 ?


DateTime2 fait des ravages si vous êtes un développeur Access essayant d'écrire Now () dans le champ en question. Juste fait une migration d'Access -> SQL 2008 R2 et il a mis tous les champs de date-heure dedans comme DateTime2. Ajout d'un enregistrement avec Now () comme valeur balisée. C'était correct le 1/1/2012 14:53:04 PM, mais pas le 10/01/2012 14:53:04.

Une fois le personnage a fait la différence. J'espère que ça aide quelqu'un.


DATETIME2 a une plage de dates de "0001/01/01" à "9999/12/31" tandis que le type DATETIME ne prend en charge que l'année 1753-9999.

En outre, si vous le souhaitez, DATETIME2 peut être plus précis en termes de temps; DATETIME est limité à 3 1/3 millisecondes, tandis que DATETIME2 peut être précis jusqu'à 100ns.

Les deux types System.DateTime à System.DateTime dans .NET - aucune différence ici.

Si vous avez le choix, je vous recommande d'utiliser DATETIME2 autant que possible. Je ne vois aucun avantage à utiliser DATETIME (sauf pour la rétrocompatibilité) - vous aurez moins de problèmes (avec des dates hors de portée et comme ça).

Plus: si vous n'avez besoin que de la date (sans partie horaire), utilisez DATE - c'est aussi bon que DATETIME2 et vous économisez de l'espace! :-) Il en va de même pour l'heure seulement - utilisez TIME . C'est ce que ces types sont là pour!


Selon cet article , si vous souhaitez avoir la même précision de DateTime en utilisant DateTime2, vous devez simplement utiliser DateTime2 (3). Cela devrait vous donner la même précision, prendre un octet de moins et fournir une plage étendue.


Bien qu'il y ait une précision accrue avec datetime2 , certains clients ne prennent pas en charge la date , l' heure ou le datetime2 et vous forcent à convertir en un littéral de chaîne. Microsoft mentionne spécifiquement les problèmes ODBC, OLE DB, JDBC et SqlClient "de niveau inférieur" avec ces types de données et dispose d'un datetime2 montrant comment chacun peut mapper le type.

Si la compatibilité de la valeur sur la précision, utilisez datetime


J'ai juste trébuché un autre avantage pour DATETIME2 : il évite un bug dans le module Python adodbapi , qui explose si une valeur de datetime bibliothèque standard est passée qui a des microsecondes non nulles pour une colonne DATETIME mais qui fonctionne bien si la colonne est définie comme DATETIME2 .


L'interprétation des chaînes de date dans datetime et datetime2 peut également être différente lorsque vous utilisez des paramètres DATEFORMAT non US. Par exemple

set dateformat dmy
declare @d datetime, @d2 datetime2
select @d = '2013-06-05', @d2 = '2013-06-05'
select @d, @d2

Cela renvoie le 2013-05-06 (c.-à-d. Le 6 mai) pour datetime et le 2013-06-05 (c.-à-d. Le 5 juin) pour datetime2 . Cependant, avec la dateformat définie sur mdy , @d et @d2 retournent 2013-06-05 .

Le comportement datetime semble en contradiction avec la documentation MSDN de SET DATEFORMAT qui stipule: Certains formats de chaînes de caractères, par exemple ISO 8601, sont interprétés indépendamment du paramètre DATEFORMAT . Évidemment pas vrai!

Jusqu'à ce que je sois mordu par ça, j'avais toujours pensé que les yyyy-mm-dd seraient juste gérées correctement, indépendamment de la langue / des paramètres régionaux.


datetime2 gagne dans la plupart des aspects sauf (compatibilité avec les anciennes applications)

  1. plus large gamme de valeurs
  2. meilleure précision
  3. espace de stockage plus petit (si la précision facultative spécifiée par l'utilisateur est spécifiée)

s'il vous plaît noter les points suivants

  • Syntaxe
    • datetime2 [(fractional secondes precision => Regardez sous la taille de stockage)]
  • Précision, échelle
    • 0 à 7 chiffres, avec une précision de 100ns.
    • La précision par défaut est de 7 chiffres.
  • Taille de stockage
    • 6 octets pour une précision inférieure à 3;
    • 7 octets pour la précision 3 et 4.
    • Toute autre précision nécessite 8 octets .
  • DateTime2 (3) a le même nombre de chiffres que DateTime mais utilise 7 octets de stockage au lieu de 8 octets ( SQLHINTS- DateTime Vs DateTime2 )
  • Plus d'informations sur datetime2 (article MSDN Transact-SQL)

Source de l'image: Kit de formation auto-rythmé MCTS (Examen 70-432): Microsoft® SQL Server® 2008 - Mise en œuvre et maintenance Chapitre 3: Tableaux -> Leçon 1: Création de tableaux -> page 66


Select ValidUntil + 1
from Documents

Le SQL ci-dessus ne fonctionnera pas avec un champ DateTime2. Il retourne et erreur "Le conflit de type d'opérande: datetime2 est incompatible avec int"

Ajouter 1 pour obtenir le jour suivant est quelque chose que les développeurs ont fait avec des dates depuis des années. Maintenant Microsoft a un champ datetime2 super nouveau qui ne peut pas gérer cette fonctionnalité simple.

"Utilisons ce nouveau type qui est pire que l'ancien", je ne le pense pas!


Presque toutes les réponses et les commentaires ont été lourds sur les avantages et la lumière sur les inconvénients. Voici un récapitulatif de tous les avantages et inconvénients jusqu'à présent, plus quelques inconvénients cruciaux (dans # 2 ci-dessous) que j'ai seulement vu mentionné une fois ou pas du tout.

  1. AVANTAGES:

1.1. Plus conforme ISO (ISO 8601) (bien que je ne sache pas comment cela entre en pratique).

1.2. Plus de portée (1/1/0001 à 31/12/9999 contre 1/1/1773-12 / 31/9999) (bien que la gamme supplémentaire, toutes antérieures à l'année 1753, ne sera probablement pas utilisée sauf pour ex., dans les applications historiques, astronomiques, géologiques, etc.).

1.3. Correspond exactement à la plage de la plage du type DateTime de .NET (bien que les deux convertissent sans aucun codage spécial si les valeurs sont dans la plage et la précision du type cible, sauf pour Con # 2.1 ci-dessous sinon erreur / arrondi).

1.4. Plus de précision (100 nanosecondes aka 0,000,000,1 sec contre 3,33 millisecondes alias 0,003,33 sec.) (Bien que la précision supplémentaire ne sera probablement pas utilisé, sauf pour ex., Dans les applications d'ingénierie / scientifiques).

1.5. Lorsqu'il est configuré pour similaire (comme dans 1 millisec pas "même" (comme dans 3,33 millisec) comme Iman Abidi a affirmé) la précision que DateTime , utilise moins d'espace (7 contre 8 octets), mais alors, bien sûr, vous perdriez le bénéfice de précision qui est probablement l'un des deux (l'autre étant la gamme) le plus vanté, bien que des avantages probablement inutiles).

  1. LES INCONVÉNIENTS:

2.1. Lors de la transmission d'un paramètre à une commande SqlCommand .NET, vous devez spécifier System.Data.SqlDbType.DateTime2 si vous transmettez une valeur en dehors de la plage et / ou de la précision de SQL Server DateTime , car elle est System.Data.SqlDbType.DateTime défaut sur System.Data.SqlDbType.DateTime .

2.2. Ne peut pas être implicitement / facilement converti en une valeur numérique à virgule flottante (nombre de jours depuis la date-heure minimale) pour effectuer les opérations suivantes avec / dans les expressions SQL Server à l'aide de valeurs numériques et d'opérateurs:

2.2.1. ajouter ou soustraire # de jours ou de jours partiels. Remarque: L' DateAdd fonction DateAdd comme solution de contournement n'est pas triviale lorsque vous devez prendre en compte plusieurs parties sinon la totalité de la date et de l'heure.

2.2.2. faire la différence entre deux dates-heures aux fins du calcul de «l'âge». Remarque: Vous ne pouvez pas simplement utiliser la fonction DateDiff SQL Server, car elle ne calcule pas l' age comme la plupart des gens s'attendraient à ce que les deux dates coïncident avec une limite date / heure du calendrier / horloge des unités spécifiées Par exemple, la DateDiff dans Day de deux dates à seulement 1 milliseconde d'intervalle retournera 1 par rapport à 0 (jours) si ces les dates et heures sont des jours calendaires différents (c.-à-d. «1999-12-31 23: 59: 59,9999999» et «2000-01-01 00: 00: 00.0000000»). Les mêmes dates-heures de différence de 1 milliseconde, si elles sont déplacées de sorte qu'elles ne traversent pas un jour calendaire, renverront un "DateDiff" en Day de 0 (jours).

2.2.3. Prenez la Avg des dates-heures (dans une requête d'agrégation) en convertissant tout d'abord en "Float", puis de nouveau en DateTime .

Remarque: pour convertir DateTime2 à un numérique, vous devez faire quelque chose comme la formule suivante qui suppose toujours que vos valeurs ne sont pas inférieures à l'année 1970 (ce qui signifie que vous perdez toute la plage supplémentaire plus un autre 217 ans. peut ne pas être en mesure d'ajuster simplement la formule pour permettre une portée supplémentaire, car vous pouvez rencontrer des problèmes de débordement numérique.

25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time) / 1.0E + 9) / 86400.0 - Source: " https://siderite.blogspot.com/2015/08/how-to-translate-t-sql-datetime2-to.html "

Bien sûr, vous pouvez aussi Cast to DateTime premier (et si nécessaire revenir à DateTime2 ), mais vous perdrez la précision et la portée (tous avant 1753) des avantages de DateTime2 rapport à DateTime qui sont les deux plus grands et aussi en même temps prolly le moins probable nécessaire qui pose la question pourquoi l'utiliser quand vous perdez les conversions implicites / faciles au nombre à virgule flottante (nombre de jours) pour l'addition / soustraction / "âge" (contre DateDiff ) / Avg calcs avantage qui est un gros dans mon expérience.

Par ailleurs, la moyenne des dates-heures est (ou devrait au moins être) un cas d'utilisation important. a) Outre l'utilisation pour obtenir la durée moyenne lorsque des dates-heures (depuis une date-base commune) sont utilisées pour représenter la durée (une pratique courante), b) il est également utile d'obtenir une statistique de type tableau de bord sur heure est dans la colonne date-heure d'une plage / groupe de lignes. c) Une requête ad-hoc standard (ou au moins devrait être standard) pour surveiller / dépanner des valeurs dans une colonne qui ne sont plus valides / peuvent être déconseillées est de lister pour chaque valeur le nombre d'occurrences et (si disponible) les Min , Avg et Max associés à cette valeur.


Vieille question ... Mais je veux ajouter quelque chose qui n'est pas déjà mentionné par quelqu'un ici ... (Note: Ceci est ma propre observation, alors ne demandez pas de référence)

Datetime2 est plus rapide lorsqu'il est utilisé dans les critères de filtre.

TLDR:

Dans SQL 2016, j'avais une table avec cent mille lignes et une colonne datetime ENTRY_TIME car il fallait stocker l'heure exacte en secondes. Lors de l'exécution d'une requête complexe avec plusieurs jointures et une sous-requête, lorsque j'ai utilisé la clause where comme:

WHERE ENTRY_TIME >= '2017-01-01 00:00:00' AND ENTRY_TIME < '2018-01-01 00:00:00'

La requête était correcte au départ lorsqu'il y avait des centaines de lignes, mais lorsque le nombre de lignes augmentait, la requête commençait à donner cette erreur:

Execution Timeout Expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.

J'ai supprimé la clause where, et de manière inattendue, la requête a été exécutée en 1 seconde, bien que maintenant toutes les lignes pour toutes les dates ont été récupérées. J'ai exécuté la requête interne avec la clause where, et cela a pris 85 secondes, et sans clause where cela a pris 0.01 secondes.

Je suis tombé sur de nombreux threads ici pour ce problème en tant que performance de filtrage de datetime

J'ai optimisé la requête un peu. Mais la vraie vitesse que j'ai obtenue était en changeant la colonne datetime en datetime2.

Maintenant, la même requête qui a expiré précédemment prend moins d'une seconde.

à votre santé


Je pense que DATETIME2 est le meilleur moyen de stocker la date, car il a plus d'efficacité que le DATETIME. Dans SQL Server 2008, vous pouvez utiliser DATETIME2, stocker une date et une heure, stocker de 6 à 8 octets et avoir une précision de 100 nanosecondes. Donc, toute personne qui a besoin d'une plus grande précision dans le temps voudra DATETIME2.


La documentation MSDN pour datetime recommande d'utiliser datetime2 . Voici leur recommandation:

Utilisez les types de données time, date, datetime2 et datetimeoffset pour un nouveau travail. Ces types sont conformes à la norme SQL. Ils sont plus portables. time, datetime2 et datetimeoffset fournissent plus de précision en secondes. datetimeoffset fournit un support de fuseau horaire pour les applications déployées globalement.

datetime2 a une plage de dates plus longue, une précision fractionnelle par défaut plus grande et une précision facultative spécifiée par l'utilisateur. En outre, en fonction de la précision spécifiée par l'utilisateur, il peut utiliser moins de stockage.





datetime2