Was tut DATEDIFF?
Heute widmen wir uns einer der bekanntesten und wirklich häufig verwendeten Funktionen im SQL Server: DATEDIFF
. Die DATEDIFF
-Funktion gehört zu den Funktionen für Zeit-Objekte, mit ihr könnt ihr die Zeit zwischen zwei Datetime-Zeitstempeln berechnen. Dabei steht es euch frei, den Abstand in Jahren, Monaten, Tagen, Stunden, Minuten, Sekunden, Millisekunden oder Nanosekunden zu bestimmen, einzig der Rückgabewert muss im Wertebereich von INT
liegen.
Wie könnt ihr mit DATEDIFF arbeiten?
Wie bereits gesagt, berechnet die DATEDIFF
-Funktion den Abstand zwischen zwei DateTime-Werten. Sehen wir uns ein Beispiel aus der WideWorldImporters Datenbank in der Sales.Orders-Tabelle
an. Wenn wir hier wissen möchten, wieviele Tage zwischen der ersten und der letzten Bestellung eines Kunden lagen, also wie lange der Kunde bereits unser Kunde ist, dann können wir das wie folgt tun:
SELECT MIN(OrderDate) AS MinOrderDate ,MAX(OrderDate) AS MaxOrderDate ,DATEDIFF(DAY, MIN(OrderDate), MAX(OrderDate)) as DaysBetweenOrders ,CustomerID FROM Sales.Orders GROUP BY CustomerID ORDER BY DATEDIFF(DAY, MIN(OrderDate), MAX(OrderDate)) DESC
Selbstverständlich können wir uns diesen Zeitabstand auch in Monaten oder Jahren ausgeben lassen:
SELECT MIN(OrderDate) AS MinOrderDate ,MAX(OrderDate) AS MaxOrderDate ,DATEDIFF(DAY, MIN(OrderDate), MAX(OrderDate)) as DaysBetweenOrders ,DATEDIFF(MONTH, MIN(OrderDate), MAX(OrderDate)) as MonthsBetweenOrders ,DATEDIFF(YEAR, MIN(OrderDate), MAX(OrderDate)) as YearsBetweenOrders ,CustomerID FROM Sales.Orders GROUP BY CustomerID ORDER BY DATEDIFF(DAY, MIN(OrderDate), MAX(OrderDate)) DESC
Wir können uns aber zum Beispiel auch ansehen, wie lange die letzte Bestellung eines Kunden nun her ist:
SELECT MIN(OrderDate) AS MinOrderDate ,MAX(OrderDate) AS MaxOrderDate ,DATEDIFF(DAY, MAX(OrderDate), GETDATE()) as DaysSinceLastOrder ,CustomerID FROM Sales.Orders GROUP BY CustomerID ORDER BY DATEDIFF(DAY, MAX(OrderDate), GETDATE()) DESC
Wenn ihr nun ausrechnen möchtet, wie viel Zeit im Schnitt zwischen den Bestellungen eines Kunden lag, könnt ihr das mit einer CTE tun:
;WITH cte_dates AS ( SELECT DATEDIFF(DAY, OrderDate, LEAD(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) ) DaysBetweenOrders ,CustomerID FROM Sales.Orders ) SELECT AVG(CAST(DaysBetweenOrders AS decimal(10,2))) ,CustomerID FROM cte_dates GROUP BY CustomerID
Wie oben bereits geschrieben, funktioniert das aber nur, so lange der Rückgabewert im Wertebereich von INT
liegt. Den maximalen Wert für einen Integer könnt ihr dabei in SQL wie folgt bestimmen:
SELECT -1-POWER(-2,31);
So erhalten wir den Wert 2147483647. Wenn wir also auf den Zeitanteil Nanosekunden schauen, dann können zwei Datumwerte, zwischen denen wir die Nanosekunden ermitteln möchten, maximal 2,15 Sekunden auseinander liegen. Probieren wir das aus, folgende Abfrage liefert einen Overflow-Fehler:
DECLARE @currentDate datetime = GETDATE() SELECT DATEDIFF(NANOSECOND, DATEADD(SECOND, -3, @currentDate), @currentDate)
Reduzieren wir das Zeitfenster hingegen auf 2 Sekunden, dann erhalten wir ein Resultat:
DECLARE @currentDate datetime = GETDATE() SELECT DATEDIFF(NANOSECOND, DATEADD(SECOND, -2, @currentDate), @currentDate)
Beschäftigen wir uns nun also damit, wie groß der Abstand zweier Zeitstempel maximal sein darf, wenn sie den maximalen INT-Wert in Millisekunden bzw. Sekunden auseinanderliegen:
DECLARE @MaxInt int = -1-POWER(-2,31); DECLARE @currentDate datetime = GETDATE() SELECT DATEDIFF(HOUR, @currentDate, DATEADD(MILLISECOND, @MaxInt, @currentDate)) MaxMSHours ,DATEDIFF(DAY, @currentDate, DATEADD(MILLISECOND, @MaxInt, @currentDate)) MaxMSDays ,DATEDIFF(YEAR, @currentDate, DATEADD(MILLISECOND, @MaxInt, @currentDate)) MaxMSYears ,DATEDIFF(HOUR, @currentDate, DATEADD(SECOND, @MaxInt, @currentDate)) MaxSHours ,DATEDIFF(DAY, @currentDate, DATEADD(SECOND, @MaxInt, @currentDate)) MaxSDays ,DATEDIFF(YEAR, @currentDate, DATEADD(SECOND, @MaxInt, @currentDate)) MaxSYears
Ihr seht, dass zwei Zeitstempel, deren Abstand ihr in Millisekunden wissen möchtet, maximal 25 Tage auseinander liegen dürfen, während zwei Zeitstempel, deren Abstand ihr in Sekunden wissen möchtet, maximal 68 Jahre auseinanderliegen können bevor es zum Overflow von INT
kommt. Das ist doch ganz schön granular für solche Zeitspannen.