In unseren Projekten dreht sich immer alles um die Zeit. Meist geht es darum, die Daten auf Tages-, Monats- oder Jahresebene zu vergleichen und zu analysieren. Vor einiger Zeit hatten wir mit einer etwas spezielleren Anforderung in einem Projekt zu tun, von der wir heute berichten möchten.

Damals ging es um die Darstellung von Messdaten aus einer Spezialanwendung, die innerhalb eines Unternehmens die Verfügbarkeit von Software-Systemen und Servern prüft. Mit dieser Spezialanwendung wird mindestens einmal innerhalb von 15 Minuten geprüft, ob das jeweilige System ansprechbar ist. So wird zum Beispiel geprüft, ob ein SAP-Login erfolgreich durchgeführt werden kann oder ob beim Aufruf einer Web-Anwendung eine bestimmte Seite zurück geliefert wird.

Über eine Schnittstelle erhalten wir die Informationen zu den nicht erfolgreichen Messungen. Anders herum gesagt: immer, wenn ein System während der Verfügbarkeitsprüfung nicht erreichbar ist, bekommen wir einen Datensatz geliefert. Als Zeitangabe erhalten wir ein Feld vom Typ DateTime, in dem auf die Millisekunde genau der Zeitpunkt steht, zu dem die Verfügbarkeitsprüfung stattgefunden hat.

Jetzt haben wir die Daten zwar auf die Millisekunde genau, aber das wollen wir gar nicht. Die Anforderung ging schließlich dahin, dass wir zählen sollten, in wie vielen Zeiteinheiten mit je 15 Minuten ein Systemausfall gemessen wurde. Dabei kam noch erschwerend hinzu, dass die Messungen nicht genau im 15-Minuten-Takt gemacht werden, sondern nur mindestens alle 15 Minuten. Also konnte es auch vorkommen, dass innerhalb von 15 Minuten mehr als ein Datensatz für ein System geliefert wurde. Das darf auf keinen Fall doppelt gezählt werden, sondern muss als eine Messung verbucht werden. Für ein System, das eine ganze Stunde nicht erreichbar ist, könnten zum Beispiel folgende Daten anfallen:

In dem Beispiel sieht man, dass die zweite und dritte Messung innerhalb der gleichen 15 Minuten – nämlich zwischen 13:15 und 13:30 – liegen. Somit ist klar, dass wir einen Weg finden müssen, um die Messdaten zuerst einmal auf 15-Minuten-Schritte aufzurunden und dann nach SystemID und Zeit zu gruppieren. Dadurch erhält man dann pro Viertelstunde und System genau einen Datensatz.

Das Runden von Zeitangaben

Um Zeitangaben mit SQL-Bordmitteln runden zu können, muss man zuerst einmal etwas haben, das man runden kann. Die gängigen Rundungsfunktionen ROUND(), FLOOR() und CEILING() arbeiten nur mit numerischen Werten. Was liegt also näher, als eine Typumwandlung von DateTime nach Float? Aber was hat man dann?

Typumwandlung des Datentyps DateTime in einen numerischen Datentyp

Die Typumwandlung eines Datumswertes in einen numerischen Wert folgt in SQL ganz einfachen Regeln:

1. Der ganzzahlige Anteil des numerischen Wertes entspricht der Anzahl Tage, die seit dem 01.01.1900 vergangen sind. Das wäre zum Beispiel für den 01.01.2014 der Wert 41.638. Dies lässt sich mit der DateDiff()-Funktion ganz einfach nachrechnen.
2. Der Anteil nach der Kommastelle entspricht dem Anteil des aktuellen Tages gemessen von 00:00:00.000 bis 23:59:59.999. 0,5 entspricht zum Beispiel 12:00 Uhr mittags. 0,25 entspricht 6:00 Uhr morgens. Mit einer Kettenrechnung kann man die Stunden, Minuten und Sekunden ausrechnen.Ein Beispiel:Angenommen wir haben folgende Nachkommastellen gegeben: 0,913224537
   • Diese Zahl multipliziert mit der Anzahl der Stunden eines Tages ergibt:
     21,9173888888326 – es ist also nach 21 Uhr.
   • Nimmt man wieder die Nachkommastellen und multipliziert diese mit der Anzahl der Minuten in einer Stunde, dann erhält man:
     55,0433333299588 – es sind also 55 Minuten der aktuellen Stunde vergangen.
   • Auch von dieser Zahl wieder die Nachkommastellen, multipliziert mit der Anzahl der Sekunden in einer Minute:
     2,59999979753047 – also: Rund 2,6 Sekunden.
   • Damit ergibt sich die Uhrzeit, die durch die oben angegebenen Nachkommastellen repräsentiert wird: Es ist 21:55:02.600.

Soll auf die Stunde genau auf- oder abgerundet werden, so muss der umgewandelte DateTime-Wert mit 24 multipliziert, danach gerundet und dann durch 24 dividiert werden. Zum Aufrunden sieht das dann z. B. so aus:

DECLARE @dt DATETIME, @f FLOAT, @rndUpHour FLOAT, @rndDwnHour FLOAT

SET @dt = '2014-01-01 21:55:02.600'  -- Zeitpunkt festlegen
SET @f = convert(FLOAT, @dt)  -- Umwandlung in Float --> 41638,913224537

-- Aufrunden bitte...
SET @rndUpHour = CEILING(@f * 24) / 24

-- Abrunden geht auch...
SET @rndDwnHour = FLOOR(@f * 24) / 24

-- Ergebnis:
SELECT
      @dt as StartZeit,
      CONVERT(SMALLDATETIME, @rndDwnHour) as StundeAbgerundet,
      CONVERT(SMALLDATETIME, @rndUpHour) as StundeAufgerundet

Hier das Ergebnis:

Abbildung 1: Ergebnis des Auf- bzw. Abrundens auf volle Stunden

Möchten wir auf die Minute genau auf- oder abrunden, so muss der numerische Wert, der sich aus der Typ­umwandlung von DateTime ergibt mit 24 und anschließend noch mit 60 multipliziert, gerundet und wieder durch 24 und durch 60 dividiert werden.

Für das Runden auf volle Viertelstunden muss anstatt mit 60 nur mit 4 multipliziert werden, da ja die Stunde auch nur in vier gleiche Teile aufgeteilt werden soll. Allgemein könnte man auch sagen, dass wir ein Raster von 15 Minuten vorgeben und die 60 Minuten einer Stunde durch dieses Raster teilen. Somit kommen wir auch wieder auf 60 / 15 = 4. Damit lässt sich aber ein schöner allgemeingültiger Ansatz realisieren, der dann so aussehen könnte:

DECLARE @dt DATETIME, @f FLOAT, @pattern INT, @factor FLOAT,
                  @rndUpPattern FLOAT, @rndDwnPattern FLOAT

SET @dt = '2014-01-01 21:55:02.600'  -- Zeitpunkt festlegen
SET @f = CONVERT(FLOAT, @dt)  -- Umwandlung in Float --> 41638,913224537
SET @pattern = 15       -- Angabe des "Ziel"-Zeitrasters (Minuten) auf das
                        -- gerundet werden soll.
                        -- Funktioniert aber nur zuverlässig fürganzzahlige
                           -- Teiler der Zahl 60! Also: 1,2,3,4,5,6,10,usw.
SET @factor = 24 * 60 / @pattern

-- Aufrunden bitte...
SET @rndUpPattern = CEILING(@f * @factor) / @factor

-- Abrunden geht auch...
SET @rndDwnPattern = FLOOR(@f * @factor) / @factor

-- Ergebnis:
SELECT
      @dt as StartZeit,
      CONVERT(SMALLDATETIME, @rndDwnPattern) as RasterAbgerundet,
      CONVERT(SMALLDATETIME, @rndUpPattern) as RasterAufgerundet

Und hier das Ergebnis:

Abbildung 2: Ergebnis des Auf-/Abrundens auf die nächste 1/4-Stunde

Für die Rückumwandlung in einen Datumswert hat sich die Verwendung des SmallDateTime-Formats als praktikabel erwiesen, da immer auf volle Minuten gerundet wird und Rundungsfehler somit eliminiert werden können.