DATEDIFF

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.

Referenzen

• Dokumentation von Microsoft