07.01.2016

Umrechnung UTC in MEZ mit Sommerzeit

Technical Value

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.   Verschiebung von Sonnenauf- und -untergang durch die Sommerzeit.

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:

  1. With Years AS
  2. (
  3. SELECT Year = 1990
  4. UNION ALL
  5. SELECT Year + 1
  6. FROM Years
  7. WHERE
  8.   Year < YEAR(GETDATE())+10
  9. ),

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.

  1. pre AS
  2. (
  3. SELECT
  4.   DatetimeUTCFrom =
  5.   CAST(DATETIMEFROMPARTS(year,3,31-datepart(weekday,datefromparts(year,3,31)) + 1,1,0,0,0) AS datetimeoffset(7))
  6.   ,DatetimeUTCTo =
  7.   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))
  8.   ,Offset = 2
  9.   ,Suffix = ''
  10. FROM Years
  11.  
  12. UNION ALL
  13. SELECT
  14.   DatetimeUTCFrom =
  15.   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))
  16.   ,DatetimeUTCTo =
  17. CAST(DATETIMEFROMPARTS(year,3,31-datepart(weekday,datefromparts(year,3,31)) + 1,1,0,0,0) AS datetimeoffset(7))
  18.   ,Offset = 1
  19.   ,Suffix = ''
  20. FROM Years
  21.  
  22. UNION ALL
  23. SELECT
  24.   DatetimeUTCFrom =
  25.   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))
  26.   ,DatetimeUTCTo =
  27.   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))
  28.   ,Offset = 2
  29.   ,Suffix = 'A'
  30. FROM Years
  31. UNION ALL
  32. SELECT
  33.   DatetimeUTCFrom =
  34.   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))
  35.   ,DatetimeUTCTo =
  36.   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))
  37.   ,Offset = 1
  38.   ,Suffix = 'B'
  39. FROM Years
  40. ),

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:

  1. utc_cet AS
  2. (
  3. SELECT
  4.   DatetimeUTCFrom
  5.   ,DatetimeUTCTo = DATEADD(NANOSECOND,-100,DatetimeUTCTo)
  6.   ,DatetimeCETFrom = SWITCHOFFSET(DatetimeUTCFrom,'+'+RIGHT('0' + CAST(Offset AS NVARCHAR(2)),2)+':00')
  7.   ,DatetimeCETTo = SWITCHOFFSET(DATEADD(NANOSECOND,-100,DatetimeUTCTo),'+'+RIGHT('0' + CAST(Offset AS NVARCHAR(2)),2)+':00')
  8.   ,SAPTimeStmpFrom =
  9.   CAST(DATEDIFF(DAY, CAST('19900101' AS DATETIME2), DatetimeUTCFrom) AS BIGINT) * 24 * 60 * 60 * 10000
  10. + DATEDIFF(MS,CAST(DatetimeUTCFrom AS DATE),DatetimeUTCFrom) * 10
  11.   ,SAPTimeStmpTo =
  12.   CAST(DATEDIFF(DAY, CAST('19900101' AS DATETIME2), DatetimeUTCTo) AS BIGINT) * 24 * 60 * 60 * 10000
  13. + DATEDIFF(MS,CAST(DatetimeUTCTo AS DATE),DatetimeUTCTo) * 10 -1
  14.   ,Offset
  15.   ,Suffix
  16. FROM pre
  17. )
  18. SELECT * FROM utc_cet

Eine mögliche Anwendung sieht dann wie folgt aus:

  1. SELECT TOP 10000
  2.   TIMESTMP,
  3.   DateTimeUTC =
  4.   CAST(
  5.     DATEADD(millisecond, (TIMESTMP % (10000 * 60 * 60 * 24)) / 10,
  6.     DATEADD(d,TIMESTMP / 10000 / 60 / 60 / 24, CAST('19900101' AS DATETIME2)))
  7.   AS datetimeoffset)
  8.   ,DatetimeCET=
  9.   SWITCHOFFSET
  10.   (
  11.     CAST(
  12.     DATEADD(millisecond, (TIMESTMP % (10000 * 60 * 60 * 24)) / 10,
  13.     DATEADD(d,TIMESTMP / 10000 / 60 / 60 / 24, CAST('19900101' AS DATETIME2)))
  14.     AS datetimeoffset)
  15.     ,'+'+RIGHT('0' + CAST(Offset AS NVARCHAR(2)),2)+':00'
  16.   )
  17.   ,offset
  18. FROM CO.COEP AS COEP
  19. JOIN utc_cet
  20. ON
  21. COEP.Timestmp BETWEEN utc_cet.SAPTimeStmpFrom AND utc_cet.SAPTimeStmpTo

  Hoffentlich kann ich dem ein oder anderen hiermit ein wenig Arbeit ersparen!

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden