Umrechnung UTC in MEZ mit Sommerzeit
Viele ERP-Systeme verwenden technisch UTC-Zeiten in ihren Tabellen. Infolgedessen ist im BI-System eine Umrechnung erforderlich, um die richtigen Taggrenzen auszuweisen. Dabei muss neben der eigentlichen Zeitzonenverschiebung auch die Umstellung der Sommerzeit beachtet werden. Diese ist in der EU-Richtlinie 2000/84/EG geregelt und dürfte sich somit nicht so schnell ändern. 
Regelung der Sommerzeit
Im weiteren betrachte ich nur die MEZ/MESZ für den Zeitraum nach 1990: Umstellung Winterzeit -> Sommerzeit: letzter Sonntag im März von 01:59:59 -> 03:00:00 Umstellung Sommerzeit-Winterzeit bis 1995: letzter Sonntag im September von 02:59:59 -> 02:00:00 Umstellung Sommerzeit-Winterzeit ab 1996: letzter Sonntag im Oktober von 02:59:59 -> 02:00:00 Da es bei der Umstellung von Sommer- auf Winterzeit die Stunde quasi doppelt gibt, gibt es zur Unterscheidung den Suffix A bzw. B.
Umsetzung mittels CTE
Zur Umsetzung mittels CTE gilt es zunächst, die Jahre ab 1990 bis 10 Jahre in die Zukunft aufzuspannen:
- With Years AS
- (
- SELECT Year = 1990
- UNION ALL
- SELECT Year + 1
- FROM Years
- WHERE
- Year < YEAR(GETDATE())+10
- ),
Jetzt die vier Zeiträume: Winterzeit, Sommerzeit und die A-Stunde und B-Stunde bei Rückstellung auf Winterzeit. Um den letzten Sonntag des Monats zu erhalten berechne ich einfach den weekday des letzten Tages und ziehe diesen vom letzten Tag ab.
- pre AS
- (
- SELECT
- DatetimeUTCFrom =
- CAST(DATETIMEFROMPARTS(year,3,31-datepart(weekday,datefromparts(year,3,31)) + 1,1,0,0,0) AS datetimeoffset(7))
- ,DatetimeUTCTo =
- CAST(DATETIMEFROMPARTS(year,CASE WHEN YEAR < 1996 THEN 9 ELSE 10 END,CASE WHEN YEAR < 1996 THEN 30 ELSE 31 END-datepart(weekday,datefromparts(year,CASE WHEN YEAR < 1996 THEN 9 ELSE 10 END,CASE WHEN YEAR < 1996 THEN 30 ELSE 31 END)) + 1,0,0,0,0) AS datetimeoffset(7))
- ,Offset = 2
- ,Suffix = ''
- FROM Years
- UNION ALL
- SELECT
- DatetimeUTCFrom =
- CAST(DATETIMEFROMPARTS(year-1,CASE WHEN YEAR -1 < 1996 THEN 9 ELSE 10 END,CASE WHEN YEAR -1 < 1996 THEN 30 ELSE 31 END-datepart(weekday,datefromparts(year-1,CASE WHEN YEAR -1 < 1996 THEN 9 ELSE 10 END,CASE WHEN YEAR-1 < 1996 THEN 30 ELSE 31 END)) + 1,2,0,0,0) AS datetimeoffset(7))
- ,DatetimeUTCTo =
- CAST(DATETIMEFROMPARTS(year,3,31-datepart(weekday,datefromparts(year,3,31)) + 1,1,0,0,0) AS datetimeoffset(7))
- ,Offset = 1
- ,Suffix = ''
- FROM Years
- UNION ALL
- SELECT
- DatetimeUTCFrom =
- CAST(DATETIMEFROMPARTS(year,CASE WHEN YEAR < 1996 THEN 9 ELSE 10 END,CASE WHEN YEAR < 1996 THEN 30 ELSE 31 END-datepart(weekday,datefromparts(year,CASE WHEN YEAR < 1996 THEN 9 ELSE 10 END,CASE WHEN YEAR < 1996 THEN 30 ELSE 31 END)) + 1,0,0,0,0) AS datetimeoffset(7))
- ,DatetimeUTCTo =
- CAST(DATETIMEFROMPARTS(year,CASE WHEN YEAR < 1996 THEN 9 ELSE 10 END,CASE WHEN YEAR < 1996 THEN 30 ELSE 31 END-datepart(weekday,datefromparts(year,CASE WHEN YEAR < 1996 THEN 9 ELSE 10 END,CASE WHEN YEAR < 1996 THEN 30 ELSE 31 END)) + 1,1,0,0,0) AS datetimeoffset(7))
- ,Offset = 2
- ,Suffix = 'A'
- FROM Years
- UNION ALL
- SELECT
- DatetimeUTCFrom =
- CAST(DATETIMEFROMPARTS(year,CASE WHEN YEAR < 1996 THEN 9 ELSE 10 END,CASE WHEN YEAR < 1996 THEN 30 ELSE 31 END-datepart(weekday,datefromparts(year,CASE WHEN YEAR < 1996 THEN 9 ELSE 10 END,CASE WHEN YEAR < 1996 THEN 30 ELSE 31 END)) + 1,1,0,0,0) AS datetimeoffset(7))
- ,DatetimeUTCTo =
- CAST(DATETIMEFROMPARTS(year,CASE WHEN YEAR < 1996 THEN 9 ELSE 10 END,CASE WHEN YEAR < 1996 THEN 30 ELSE 31 END-datepart(weekday,datefromparts(year,CASE WHEN YEAR < 1996 THEN 9 ELSE 10 END,CASE WHEN YEAR < 1996 THEN 30 ELSE 31 END)) + 1,2,0,0,0) AS datetimeoffset(7))
- ,Offset = 1
- ,Suffix = 'B'
- FROM Years
- ),
Bezug zu SAP COEP
Auslöser für diese Aufgabenstellung war die Umrechnung des TIMESTMP aus der SAP-Tabelle COEP. Diese weist 10000stel Sekunden seit dem 01.01.1990 aus. Hier muss man ein wenig aufpassen, da man schnell den Wertebereich von INT verlässt - daher die etwas umständliche Umrechnung:
- utc_cet AS
- (
- SELECT
- DatetimeUTCFrom
- ,DatetimeUTCTo = DATEADD(NANOSECOND,-100,DatetimeUTCTo)
- ,DatetimeCETFrom = SWITCHOFFSET(DatetimeUTCFrom,'+'+RIGHT('0' + CAST(Offset AS NVARCHAR(2)),2)+':00')
- ,DatetimeCETTo = SWITCHOFFSET(DATEADD(NANOSECOND,-100,DatetimeUTCTo),'+'+RIGHT('0' + CAST(Offset AS NVARCHAR(2)),2)+':00')
- ,SAPTimeStmpFrom =
- CAST(DATEDIFF(DAY, CAST('19900101' AS DATETIME2), DatetimeUTCFrom) AS BIGINT) * 24 * 60 * 60 * 10000
- + DATEDIFF(MS,CAST(DatetimeUTCFrom AS DATE),DatetimeUTCFrom) * 10
- ,SAPTimeStmpTo =
- CAST(DATEDIFF(DAY, CAST('19900101' AS DATETIME2), DatetimeUTCTo) AS BIGINT) * 24 * 60 * 60 * 10000
- + DATEDIFF(MS,CAST(DatetimeUTCTo AS DATE),DatetimeUTCTo) * 10 -1
- ,Offset
- ,Suffix
- FROM pre
- )
- SELECT * FROM utc_cet
Eine mögliche Anwendung sieht dann wie folgt aus:
- SELECT TOP 10000
- TIMESTMP,
- DateTimeUTC =
- CAST(
- DATEADD(millisecond, (TIMESTMP % (10000 * 60 * 60 * 24)) / 10,
- DATEADD(d,TIMESTMP / 10000 / 60 / 60 / 24, CAST('19900101' AS DATETIME2)))
- AS datetimeoffset)
- ,DatetimeCET=
- SWITCHOFFSET
- (
- CAST(
- DATEADD(millisecond, (TIMESTMP % (10000 * 60 * 60 * 24)) / 10,
- DATEADD(d,TIMESTMP / 10000 / 60 / 60 / 24, CAST('19900101' AS DATETIME2)))
- AS datetimeoffset)
- ,'+'+RIGHT('0' + CAST(Offset AS NVARCHAR(2)),2)+':00'
- )
- ,offset
- FROM CO.COEP AS COEP
- JOIN utc_cet
- ON
- COEP.Timestmp BETWEEN utc_cet.SAPTimeStmpFrom AND utc_cet.SAPTimeStmpTo
Hoffentlich kann ich dem ein oder anderen hiermit ein wenig Arbeit ersparen!
Neuen Kommentar schreiben