Ein Hoch auf die ISO 8601

Immer Ärger mit der Zeit

Immer wieder sollen Produktionsdaten zeitlich eingeordnet und in Berichten zusammengestellt werden. Das sind Verbrauchswerte oder erzeugte Produkte. Und immer wieder ist wenigstens für mich der Aufwand ärgerlich, mit Datum, Uhrzeit, Schichtmodellen, Sommer/Winterzeit und so weiter rumzurechnen, bzw. die Daten per SQL irgendwo herauszufischen.

Die Ursache des Aufwands liegt für mich in der Genauigkeit. Der Datentyp Datetime oder auch die Unix time() ist immer bis auf die Sekunde genau. Aber welcher Zeitpunkt gilt für eine ganze Woche? 23:59:59 am Sonntag? 00:00:00 am Montag? Und wenn, wie beim Beispielkunden, die Woche von Sonntag bis Samstag geht und die Daten vom Werk in die Konzernzentrale in einer anderen Zeitzone berichtet werden? Gleiches gilt für Tage (Sommerzeit, Zeitzonen) und Monate.

Wenn ich einen Wochenwert abspeichern möchte, dann mit der Angabe Jahr-Woche. Schichtwerte mit der Angabe Jahr-Monat-Tag-Schicht, usw. Dann kann ich sie auch per SQL einfach selektieren.

Also wie geht das? Dafür gibt es die Schreibweisen der ISO-8601. Ausführliche Beschreibungen gibt es hinter den unten angegebenen Links. Für unsere Zwecke – Produktionsdaten mit Zeitangaben – reichen folgende Konventionen:

Tagesdatum: ’YYYY-MM-DD’ – Eigentlich ’[YY]YY[-]MM[-]DD’, aber wenn man die ’-’ und oder das Jahrhundert weglässt, kann das in einem SQL-Query missverständlich werden. Also: Jahr-Monat-Tag immer mit 4-stelligem Jahr und ’-’ als Trenner.

Die Angaben können von rechts weggelassen werden: YYYY-MM = Monatswert, YYYY = Jahreswert.

Eine Erweiterung der ISO möchte ich mir herausnehmen: ’YYYY-MM-DD"S"S’ für die Schicht, zum Beispiel 2009-02-05S2 = Zweite Schicht am 5.2.2009. Richtig nett wird es nämlich, wenn man sekundengenaue Zeitstempel bei Schichtmodellen verwendet. Dann muss man sämtliche Schichtmodelle archivieren, um vor der eigentlichen Abfrage herauszufinden, welches Schichtmodell an welchem Tag gültig war. Die Beantwortung der Frage, wie hoch die Ausschussrate in allen ersten Schichten der vergangenen 12 Monate war ist damit nicht mehr trivial: Sonderschichten, Sommer/Winterzeit, ausgefallene Schichten („Montags fangen wir immer erst zur Spätschicht an“), usw.

Wochendatum ’YYYY-"W"WW[-D]’ – Das ist eine Kalenderwoche mit optionalem Tag in der Woche. Beispiel: 2009-W07 = Kalenderwoche 7 in 2009 oder 2009-W07-3 = Der dritte Tag in KW7 in 2009. Dabei ist die ISO-Kalenderwoche sehr genau definiert; Montag – Sonntag, erste Kalenderwoche ist die mit mindestens 4 Tagen. Das kollidiert allerdings manchmal mit der Arbeitswoche einiger Firmen.

Die Wochentagskodierung mit angehängtem -D zu verwenden sollte man sich gut überlegen. Hier ist die Umrechnung oder der Vergleich mit den anderen Tages-Kodierungen wie YYYY-MM-DD oder YYYY-DDD nicht so einfach. Am besten verwendet man so häufig wie möglich die gleiche Kodierung, um sich Umrechnungen und kombinierte Abfragen (UNION) zu sparen.

Uhrzeiten ’HH[:MM[:SS]]’ – Uhrzeiten können laut ISO mit einem Leerzeichen oder „T“ dazwischen an jedes Tagesdatum angehängt werden. Wieder kann von rechts weggelassen werden: HH:MM = Minute, HH = Stunde.

Zeitspannen, „P“ – Zwei Datum/Zeitangaben können laut ISO mit einem „P“ kombiniert werden, um die Zeitspanne zwischen ihnen zu kodieren. Beispiele: 2000P2008 = Jahre 2000 bis 2008, 2007-01P2007-03 = Erstes Quartal 2007, 2008-W10P2008-W19 = Wochen 10-19 in 2008 komplett.

Als Zeitangabe in einer Datenbank ist mir das noch nicht begegnet. Ich würde es auf den ersten Blick auch ungern verwenden, weil ungeschickte Abfragen mit Mustervergleichen versehentlich auch diese Werte liefern könnten.

SQL-Abfragen für ISO-8601 Datums- und Zeitangaben

Wenn wir jetzt eine große Tabelle mit gemischten Datumswerten haben, zum Beispiel meine geliebten Energie- und Verbrauchszahlen als Tages-, Wochen- und Monatssummen; wie kriegen wir dann die gewünschten Daten heraus? Das hängt von der verwendeten Datenbank ab.

Am komfortabelsten ist MySQL, weil es automatisch von String in Datum umrechnet. Wir nehmen eine Tabelle mit den Spalten tagname, zeit und wert. Zeit ist als varchar() definiert und kann alle oben angegebene Datumsformate enthalten.

Beispiele:

(1) Alle Tageswerte der letzten sieben Tage:

select * from isozeit
where zeit like ’____-__-__’
and zeit > subdate(now(), interval 7 day)

Der Mustervergleich ’____-__-__’ selektiert das Tagesformat, das nach obigen Konventionen eindeutig ist. Deshalb die Pflicht, den ’-’ zwischen die Felder zu schreiben.

(2) Wochenwerte dieses Jahres:

select * from isozeit
where zeit like concat(year(now()), ’-W__’)

Die Abfrage ist sicher, weil sie optional angehängte Wochentage (’-D’) ausblendet.

(3) Werte mehrerer Wochen, z.B. Woche 10-19, wie oben erwähnt:

select * from isozeit
where time like ’____-W__’
and zeit between concat(year(now()), ’-W10’) and concat(year(now()), ’-W19’)

Die Wochennummern kann man natürlich auch relativ berechnen. Leider ist das „-W“ im between-Teil allein nicht ausreichend, um angehängte Wochentage auszuschliessen, daher das zusätzliche like ’____-W__’.

(4) Wochenwerte der vergangenen Woche:

select * from isozeit
where zeit = date_format(subdate(now(), interval 1 week), ’%x-W%v’)

Hier glänzt MySQL richtig. Date_format() kann nämlich verschiedene Wochenzählweisen und liefert dann Jahr und Woche entsprechend.

Beispiel:

select date_format(’2008-12-31’, ’%x-W%v’), date_format(’2009-01-01’, ’%x-W%v’),
date_format(’2008-12-31’, ’%X-W%V’), date_format(’2009-01-01’, ’%X-W%V’)

liefert:

’2009-W01’, ’2009-W01’, ’2008-W52’, ’2008-W52’

’%x-W%v’ ist also die europäische (und ISO-)Zählung; ’%X-W%V’ die US-/UK-Zählung.

Bei anderen DBs wird es etwas komplizierter. MS-SQL kennt datepart(), aber die Art der Wochenzählung kann nur global eingestellt werden. Könnte man den Wochencode auch aus year() und week() zusammensetzen, zum Beispiel mit

concat(year(subdate(now(), interval 1 week)), ’-W’, week(subdate(now(), interval 1 week)))

Leider nicht. Wenn die Woche 1 des aktuellen Jahres im vergangenen Jahr beginnt, liefert year() möglicherweise bereits das aktuelle Jahr und week() 0. Beweis:

select yearweek(’2009-01-01’), year(’2009-01-01’), week(’2009-01-01’)

(5) Tageswerte der vergangenen Woche, z.B. für einen Wochenreport:

select * from isozeit
where zeit like ’____-__-__’
and zeit
between from_days(to_days(now()) - (7 + weekday(now())))
and from_days(to_days(now()) - (1 + weekday(now())))

Uff. Das ist nicht ganz einfach, aber die scheinbar einfachere Lösung,

where date_format(zeit, ’%x%v’) = date_format(subdate(now(), interval 1 week), ’%x%v’)

würde bewirken, dass der erste date_format()-Term für jeden Datensatz durchgeführt würde.

Noch etwas flinker wäre vielleicht

zeit between date_format(from_days(to_days(now()) - (7 + weekday(now()))), ’%Y-%m-%d’) and date_format(from_days(to_days(now()) - weekday(now())), ’%Y-%m-%d’)

aber wahrscheinlich wandelt MySQL die from_days()-Terme schon gleich vor dem ersten Vergleich zu Strings. Ich konnte jedenfalls keinen Unterschied messen.

(6) Monatssummen aus Tageswerten bilden:

select tagname, year(zeit), month(zeit), sum(wert) from isozeit
where time like ’____-__-__’
group by 1,2,3
order by 1,2,3

Das ist einfach.

Allerdings weiss man noch nicht, ob auch für alle Tage des jeweiligen Monats Daten vorliegen, die Summe also korrekt ist. Das kann man durch eine zusätzliche Spalte

count(wert) = day(last_day(zeit))

ermitteln. Hier steht 1 für korrekt und 0 für fehlerhaft. Last_day() ist wieder ein Pluspunkt für MySQL. Es liefert das Datum des letzten Tages im angegebenen Monat.

(7) Wochensumme aus Tageswerten? Okay.

select tagname, date_format(zeit, ’%x-W%v’), sum(wert), count(wert) = 7 from isozeit
where zeit like ’____-__-__’
group by 1,2
order by 1,2

Gut, dass Wochen immer sieben Tage haben.

(8) und zum Schluss: Sind die Wochenwerte gleich der Summe der Tagswerte der Woche? Das sieht zunächst einfach aus:

select a.tagname, a.woche, a.summe, b.summe, a.korrekt, a.summe=b.summe
from
( select tagname, date_format(zeit, ’%x-W%v’) as woche, sum(wert) as summe, (count(wert) = 7) as korrekt
from isozeit where zeit like ’____-__-__’ group by 1,2 order by 1,2
) a
join
( select tagname, zeit as woche, wert as summe, 1 as korrekt
from isozeit where zeit like ’____-W__’ group by 1,2 order by 1,2
) b
on a.tagname=b.tagname and a.woche=b.woche

Subselect a kennen wir aus (7). Subselect b sind die Wochenwerte. Nur macht mir auf meinem Rechner MySQL einen Strich durch die Richtung, weil es zwar bei Typen tolerant ist, aber nicht bei Zeichensätzen. Die stimmen bei den Subselects wegen der Berechnung nicht überein, deshalb musste ich bei mir folgendes schreiben:

select a.tagname, a.woche, a.summe, b.summe, a.korrekt, a.summe=b.summe
from
( select
convert(tagname using latin1) as tagname,
convert(date_format(zeit, ’%x-W%v’) using latin1) as woche,
convert(sum(wert) using latin1) as summe,
convert((count(wert) = 7) using latin1) as korrekt
from isozeit where zeit like ’____-__-__’ group by 1,2 order by 1,2
) a
join
( select
convert(tagname using latin1) as tagname,
convert(zeit using latin1) as woche,
convert(wert using latin1) as summe,
convert(1 using latin1) as korrekt
from isozeit where zeit like ’____-W__’ group by 1,2 order by 1,2
) b
on a.tagname=b.tagname and a.woche=b.woche

Das soll an Beispielen reichen. Ähnliche Abfragen für die anderen Formate können ziemlich direkt abgeleitet werden.

Links

Wikipedia (dt): ISO-8601
Wikipedia (en): ISO-8601
The Mathematics of the ISO 8601 Calendar
http://www.dmoz.org/Science/Reference/Standards/Individual_Standards/ISO/ISO_8601/

2 Antworten zu Ein Hoch auf die ISO 8601

  1. […] Im Open-MES-Blog gibt einen Artikel zu Produktionsdaten mit Zeitstempel (ISO 8601) und SQL-Abfragen dafür. […]

  2. […] Alles Strings? Bei MySQL als Datenbank und PHP als Skriptsprache ja, denn da ist die Konvertierung einfach. Der Speicherplatz stellt bei der Anzahl der Zeilen auch kein gravierendes Problem dar. Man gewinnt aber eine Menge Komfort bei den Abfragen aus dem Report-Tool. Der Trick steckt ausserdem in der Kodierung der Zeit als ISO-8601-String, siehe [ISO-Zeitkodierung]. […]

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: