Konvertierung zwischen Zeitzonen unter Berücksichtigung der Sommerzeit

Question

Hallo, ich suche bisher vergeblich eine performante Möglichkeit, ein UTC-datetime unter MSSQL in eine beliebige Zeitzone umzuwandeln, wobei die Sommerzeit berücksichtigt werden muss. Auch die neuen datetime-Typen mit offset bringen mich da nicht weiter, da hier nur eine Zeitzone berücksichtigt werden kann.
Unter MySQL benutzte ich einfach dafür die Funktion CONVERT_TZ(datetime, from_timezone, to_timezone), eine derartige Funktion scheint es bei MSSQL leider nicht zu geben. Ich habe mir jetzt mit einer Hilfstabelle timezone(name = Name der Zeitzone, time = Zeitpunkt ab wann dieser Offset gilt, offset = Offset zu UTC in Min.) und folgender Abfrage zur Umwandlung ausgeholfen, was bei großen Resultsets leider ziemlich langsam ist (siehe meine andere Frage ).

(SELECT TOP 1 DATEADD(MINUTE, offset, '2011-02-11 13:20:00') 
FROM timer.timezone 
WHERE name = 'Europe/Berlin' AND time < '2011-02-11 13:20:00'
ORDER BY time DESC)

PS: Die Umwandlung kann nicht in der Anwendung erfolgen, da die Auswahl der Zeilen abhängig von dem konvertierten datetime ist (Eingrenzung nach Tagen in lokaler Zeit).

Answer 1

Hallo mhofmann84,

ich setze die Funktion für CET/CEST ein:

 

use tempdb
go
--liefert cet/cest Zitezone zurück 
create function [dbo].[GetTimeZoneFromUtc]
(
	@datetime_utc datetime
)
returns tinyint
as
begin
	declare @select_year int
	declare @sommer_zeit_start datetime
	declare @sommer_zeit_ende datetime

	set @select_year = year(@datetime_utc)
	set @sommer_zeit_start = cast(@select_year as varchar(10)) + '-03-31'
	set @sommer_zeit_ende = cast(@select_year as varchar(10)) + '-10-31'
	 
	while datename(dw,@sommer_zeit_start) not in ('Sunday','Sonntag')
	begin
		set @sommer_zeit_start = dateadd(dd,-1,@sommer_zeit_start)
	end

	set @sommer_zeit_start = dateadd(hh,1,@sommer_zeit_start)--utc

	while datename(dw,@sommer_zeit_ende) not in ('Sunday','Sonntag')
	begin
		set @sommer_zeit_ende = dateadd(dd,-1,@sommer_zeit_ende)
	end

	set @sommer_zeit_ende = dateadd(second,3599,@sommer_zeit_ende)
	 
	declare @time_zone tinyint = 1

	select @time_zone = 2 where (
					@datetime_utc between
					@sommer_zeit_start
					and @sommer_zeit_ende
				)
	return @time_zone--Timezone [1 für CET, 2 für CEST]
end
go

declare @sample_utc_date1 datetime = '2011-01-01 10:00:00'
declare @sample_utc_date2 datetime = '2011-06-01 10:00:00'

--test1
select @sample_utc_date1 as utc_date
	, dbo.GetTimeZoneFromUtc(@sample_utc_date1) as time_zone_cet
	, dateadd(hh,dbo.GetTimeZoneFromUtc(@sample_utc_date1),@sample_utc_date1) as local_cet_date
--test2
select @sample_utc_date2 as utc_date
	, dbo.GetTimeZoneFromUtc(@sample_utc_date2) as time_zone_cest
	, dateadd(hh,dbo.GetTimeZoneFromUtc(@sample_utc_date2),@sample_utc_date2) as local_cest_date
go
--drop
drop function dbo.GetTimeZoneFromUtc
go

Answer 2

Hallo,

für die Zeitumstellung gibt es klare & einfache Regeln; von einigen Ausnahmen mal abgesehene, wie man bei Wiki Sommerzeit nachlesen kann. Entsprechend kann man berechnen, zu welcher UTC Zeit welcher Offset gilt.

Das habe ich mal als Function umgesetzt, wobei im es mir einfach gemacht und nur die Zeitumstellungen für Deutschland und nur ab 1980 berücksichtige ; 16-18 und 45-49 gab es auch noch welche wieder noch mehr Ausnahmen.

So sieht es in T-SQL aus; ich habe es zwar mit ein paar Werten getestet; eine 100% Garantie auf exakte Funktion kann ich aber nicht geben.

IF NOT OBJECT_ID('dbo.fnGetMezTimeOffSetDE', 'FN') IS NULL

     DROP FUNCTION dbo.fnGetMezTimeOffSetDE

GO

 

-- Funktion zum berechnen des TimeOffsets aus Utc Datum-Uhrzeit für DE

CREATE FUNCTION dbo.fnGetMezTimeOffSetDE

    (@utcTime datetime)

    RETURNS smallint

AS

BEGIN

 

     DECLARE @year smallint;

     DECLARE @yearString varchar(4);

     DECLARE @stdBgn datetime;

     DECLARE @stdEnd datetime;

     DECLARE @bgn varchar(30);

     DECLARE @end varchar(30);

     DECLARE @offset smallint;

 

     -- Standard = UTC +1

     SET @offset = 1;

    

     -- NULL Prüfung; alternative RAISERROR

     IF @utcTime IS NULL

          RETURN @offset;

         

     SET @year = YEAR(@utcTime);

     SET @yearString = CONVERT(varchar(4), @year);

 

     -- Sommerzeiten vor 1980 nicht berücksichtigen

     IF @year < 1980

          RETURN @offset;

         

     IF @year >= 1980

     BEGIN

          IF @year = 1980

          BEGIN

                SET @bgn = @yearString + '-10-01';

                SET @end = @yearString + '-04-07';

          END

          IF @year BETWEEN 1981 AND 1995

          BEGIN

                -- Umstellung im September; Rechenbasis 01.10.

                SET @bgn = @yearString + '-10-01'

                SET @end = @yearString + '-04-01'

          END 

          IF @year > 1995

          BEGIN

                -- Umstellung im Oktober; Rechenbasis 01.11.

                SET @bgn = @yearString + '-11-01'

                SET @end = @yearString + '-04-01'

          END 

               

          SET @stdBgn = (SELECT DATEADD(dd

                                       ,1 -DATEPART(dw ,CONVERT(datetime, @bgn, 120))

                                       ,CONVERT(datetime, @bgn + ' 01:00:00.000', 120)));

          SET @stdEnd = (SELECT DATEADD(dd

                                       ,1-DATEPART(dw, CONVERT(datetime, @end, 120))

                                       ,CONVERT(datetime, @end +' 01:00:00.000', 120)));

         

          -- Liegt Zeitpunkt ausserhalb der Standardzeiten   

          IF @utcTime >= @stdEnd AND @utcTime < @stdBgn

              SET @offset = 2;

          ELSE

              SET @offset = 1;

       

     END

     RETURN @offset;

END

GO

 

-- Test mit ein paar Werten

;WITH dates

AS (SELECT {ts N'1970-01-01 00:00:00.000'} AS Zeit UNION ALL

    SELECT {ts N'1980-04-06 00:59:00.000'} UNION ALL

    SELECT {ts N'1980-04-06 01:00:00.000'} UNION ALL

    SELECT {ts N'1980-09-28 00:59:00.000'} UNION ALL

    SELECT {ts N'1980-09-28 01:00:00.000'} UNION ALL

    SELECT {ts N'2011-03-27 00:59:00.000'} UNION ALL

    SELECT {ts N'2011-03-27 01:00:00.000'} UNION ALL

    SELECT {ts N'2011-10-30 00:59:00.000'} UNION ALL

    SELECT {ts N'2011-10-30 01:00:00.000'}

   )

SELECT *

      ,dbo.fnGetMezTimeOffSetDE(Zeit) AS UtcOffset

FROM dates

GO

 

Ergebnis:

Zeit                    UtcOffset

----------------------- ---------

1970-01-01 00:00:00.000 1

1980-04-06 00:59:00.000 1

1980-04-06 01:00:00.000 2

1980-09-28 00:59:00.000 2

1980-09-28 01:00:00.000 1

2011-03-27 00:59:00.000 1

2011-03-27 01:00:00.000 2

2011-10-30 00:59:00.000 2

2011-10-30 01:00:00.000 1

 

 

 

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 3

@Yury Iwtschenko der Anfangstag der Sommerzeit hat in der Vergangenheit leider variiert, so dass eine feste Vorschrift nur für aktuelle Zeiten gültig ist, um auch ältere Zeitpunkte umzuwandeln müssen mehrere Regeln implementiert werden, wie Olaf Helper es getan hat. Ich benötige die Regeln allerdings nicht nur für Deutschland, sondern weltweit. Aber ich werde es jetzt so lösen, dass ich die Regeln hier für Deutschland fest übernehme und nur in anderen Fällen die Tabelle abfrage - das sollten Ausnahmen bleiben und die Anwendung damit insgesamt performant genug sein.