Lorsque vous travaillez avec des dates et des heures dans SQL Server, il y a souvent des situations où vous devez arrondir une valeur datetime à une précision spécifique, telle qu’un jour, une heure, une minute ou une seconde. Cependant, déterminer la méthode la plus efficace pour effectuer cet arrondi peut être un défi.
Lors d’une conversation récente avec un expert SQL Server, Jeremiah Peschka, j’ai découvert qu’il existe plusieurs méthodes pour arrondir les dates en TSQL, mais il n’était pas clair quelle méthode était la plus efficace. Pour le savoir, j’ai décidé d’effectuer des tests et de comparer les performances de chaque méthode.
Arrondi au jour
Un des scénarios les plus courants est d’arrondir une valeur datetime au niveau du jour. Par exemple, au lieu de ‘1/4/2011 6:15:03.393921’, vous pouvez vouloir simplement ‘1/4/2011’. Heureusement, SQL Server 2008 a introduit le type de données “date”, ce qui rend cette tâche beaucoup plus facile. Vous pouvez simplement convertir une valeur datetime ou datetime2 en date, et vous obtiendrez le résultat souhaité. De plus, la nouvelle valeur est stockée sur seulement 3 octets, ce qui la rend plus efficace.
SELECT CAST('1/1/2010 23:59:59.000' AS DATE) AS [Je suis une date!]
Arrondi aux heures, minutes ou secondes
Cependant, l’arrondi à d’autres précisions, telles que les heures, les minutes ou les secondes, peut être plus compliqué. Les types de données de SQL Server ont des opinions différentes sur ce que signifie l’arrondi. Par exemple, le type de données SMALLDATETIME arrondit les minutes et les valeurs de date vers le haut, tandis que le type de données DATE ne le fait pas. Cela peut entraîner de la confusion et des résultats inattendus.
Lorsque vous décidez d’utiliser SMALLDATETIME, il est important de comprendre si vous voulez arrondir vers le haut ou vers le bas pour les minutes et les valeurs de date. La plupart du temps, nous voulons arrondir vers le bas et obtenir le plus grand nombre de minutes inférieur ou égal à la valeur datetime. Cependant, SMALLDATETIME ne fournit pas ce comportement, il doit donc être utilisé avec prudence.
Comparaison des méthodes d’arrondi des dates
Maintenant, comparons l’efficacité des différentes méthodes d’arrondi des dates. Pour cela, nous allons créer une table avec un grand nombre de valeurs datetime et mesurer les performances de SQL Server lorsqu’il travaille avec ces dates.
-- Créer une table avec des valeurs datetime
CREATE TABLE dbo.Dates (DateValue DATETIME2(7))
-- Insérer un grand nombre de valeurs datetime
DECLARE @startDate DATETIME2(7) = '2010-01-01 00:00:00',
@endDate DATETIME2(7) = '2010-01-31 23:59:59'
;WITH DatesCTE AS (
SELECT @startDate AS [Date]
UNION ALL
SELECT DATEADD(SECOND, 1, [Date])
FROM DatesCTE
WHERE [Date] < @endDate
)
INSERT INTO dbo.Dates
SELECT [Date]
FROM DatesCTE
OPTION (MAXRECURSION 0)
-- Effectuer l'arrondi en utilisant différentes méthodes
SELECT CAST(CONVERT(CHAR(10), DateValue, 120) AS DATE) AS [DateArrondie1],
DATEADD(DAY, DATEDIFF(DAY, 0, DateValue), 0) AS [DateArrondie2],
CAST(CONVERT(CHAR(10), DateValue, 120) AS DATETIME2(0)) AS [DateArrondie3]
FROM dbo.Dates
Après avoir exécuté ces requêtes, nous pouvons comparer le temps CPU et les performances de chaque méthode. Dans mes tests, j’ai constaté que l’utilisation de la fonction DATEADD pour calculer le nombre de minutes depuis une date donnée, puis les ajouter à nouveau, était la méthode la plus efficace. Cela est dû au fait que les opérations mathématiques sur les valeurs entières sont plus rapides que les conversions entre les types de données basés sur des caractères.
Il convient de noter que le format de stockage interne du type de données datetime2 n’est pas explicitement documenté dans les livres en ligne de SQL Server. Cependant, sur la base des résultats de performance, il semble que datetime2 apprécie les opérations mathématiques, ce qui suggère qu’il peut être stocké sous la forme de deux entiers de quatre octets.
En conclusion, lors de l’arrondi des dates dans SQL Server, il est important de prendre en compte la précision dont vous avez besoin et de choisir la méthode la plus efficace pour votre scénario spécifique. En comprenant le comportement des différents types de données et en comparant leurs performances, vous pouvez optimiser vos requêtes et améliorer l’efficacité globale de votre base de données SQL Server.