Rozwiązałem ten problem, mając bardzo prostą tabelę kalendarza - każdy rok ma jeden wiersz na obsługiwaną strefę czasową , ze standardowym przesunięciem i początkową datą / końcową datą czasu DST i jego przesunięciem (jeśli ta strefa czasowa to obsługuje). Następnie wbudowana, powiązana ze schematem funkcja o wartościach przechowywanych w tabeli, która zajmuje czas źródłowy (oczywiście w UTC) i dodaje / odejmuje przesunięcie.
To oczywiście nigdy nie zadziała wyjątkowo dobrze, jeśli raportujesz w stosunku do dużej części danych; partycjonowanie może wydawać się pomocne, ale nadal będziesz mieć przypadki, w których ostatnie kilka godzin w ciągu jednego roku lub kilka pierwszych godzin w następnym roku faktycznie należy do innego roku po konwersji do określonej strefy czasowej - więc nigdy nie możesz uzyskać prawdziwej partycji izolacja, z wyjątkiem sytuacji, gdy zakres raportowania nie obejmuje 31 grudnia lub 1 stycznia.
Jest kilka dziwnych przypadków, które należy wziąć pod uwagę:
2014-11-02 05:30 UTC i 2014-11-02 06:30 UTC oba przeliczają się na 01:30 AM we wschodniej strefie czasowej, na przykład (jeden po raz pierwszy 01:30 został trafiony lokalnie, a następnie jeden po raz drugi zegary cofały się od 2:00 do 1:00 i upłynęły kolejne pół godziny). Musisz więc zdecydować, jak poradzić sobie z tą godziną raportowania - zgodnie z UTC, powinieneś zobaczyć podwójny ruch lub wielkość tego, co mierzysz, gdy te dwie godziny zostaną zmapowane do jednej godziny w strefie czasowej, w której obserwuje się czas letni. Może to również grać w fajne gry z sekwencjonowaniem wydarzeń, ponieważ coś, co logicznie musiało się wydarzyć po tym, co mogło się pojawićnastąpi przed tym, gdy czas zostanie ustawiony na jedną godzinę zamiast dwóch. Skrajnym przykładem jest wyświetlenie strony, które nastąpiło o 05:59 UTC, a następnie kliknięcie, które nastąpiło o 06:00 UTC. W czasie UTC nastąpiło to w odstępie minuty, ale po przeliczeniu na czas wschodni widok nastąpił o 01:59, a kliknięcie nastąpiło godzinę wcześniej.
2014-03-09 02:30 nigdy nie zdarza się w USA. Jest tak, ponieważ o 2:00 rano przesuwamy zegary do 3:00 rano. Prawdopodobnie będziesz chciał zgłosić błąd, jeśli użytkownik wprowadzi taki czas i poprosi o konwersję na UTC lub zaprojektowanie formularza, aby użytkownicy nie mogli wybrać takiego czasu.
Nawet biorąc pod uwagę te skrajne przypadki, nadal uważam, że masz właściwe podejście: przechowuj dane w UTC. Znacznie łatwiej jest mapować dane do innych stref czasowych z UTC niż z jednej strefy czasowej do innej strefy czasowej, szczególnie gdy różne strefy czasowe rozpoczynają / kończą czas letni w różnych datach, a nawet ta sama strefa czasowa może przełączać się przy użyciu różnych reguł w różnych latach ( na przykład USA zmieniły zasady około 6 lat temu).
Do tego wszystkiego będziesz chciał użyć tabeli kalendarza, a nie jakiegoś gigantycznego CASE wyrażenia (nie instrukcji ). Właśnie napisałem na ten temat trzyczęściową serię dla MSSQLTips.com ; Myślę, że trzecia część będzie dla Ciebie najbardziej użyteczna:
http://www.mssqltips.com/sqlservertip/3173/handle-conversion-between-time-zones-in-sql-server--part-1/
http://www.mssqltips.com/sqlservertip/3174/handle-conversion-between-time-zones-in-sql-server--part-2/
http://www.mssqltips.com/sqlservertip/3175/handle-conversion-between-time-zones-in-sql-server--part-3/
Tymczasem prawdziwy przykład na żywo
Powiedzmy, że masz bardzo prostą tabelę faktów. Jedyny fakt, na którym mi zależy w tym przypadku, to czas wydarzenia, ale dodam bezsensowny identyfikator GUID, aby stół był wystarczająco szeroki, aby o niego dbać. Ponownie, mówiąc wprost, tabela faktów przechowuje zdarzenia tylko w czasie UTC i UTC. Dodałem nawet kolumnę, _UTCaby nie było zamieszania.
CREATE TABLE dbo.Fact
(
EventTime_UTC DATETIME NOT NULL,
Filler UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID()
);
GO
CREATE CLUSTERED INDEX x ON dbo.Fact(EventTime_UTC);
GO
Teraz załadujmy do naszej tabeli faktów 10 000 000 wierszy - co 3 sekundy (1200 wierszy na godzinę) od 30.12.2013 o północy do czasu UTC do pewnego czasu po 5 rano UTC w dniu 12.12.2014. Zapewnia to, że dane przekraczają granicę roku, a także czas letni do przodu i do tyłu dla wielu stref czasowych. To wygląda naprawdę przerażająco, ale zajęło mi ~ 9 sekund w moim systemie. Tabela powinna zakończyć się na około 325 MB.
;WITH x(c) AS
(
SELECT TOP (10000000) DATEADD(SECOND,
3*(ROW_NUMBER() OVER (ORDER BY s1.[object_id])-1),
'20131230')
FROM sys.all_columns AS s1
CROSS JOIN sys.all_columns AS s2
ORDER BY s1.[object_id]
)
INSERT dbo.Fact WITH (TABLOCKX) (EventTime_UTC)
SELECT c FROM x;
I tylko, aby pokazać, jak będzie wyglądać typowe zapytanie wyszukiwania w przypadku tabeli wierszy 10 mm, jeśli uruchomię to zapytanie:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, EventTime_UTC), 0),
COUNT(*)
FROM dbo.Fact
WHERE EventTime_UTC >= '20140308'
AND EventTime_UTC < '20140311'
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, EventTime_UTC), 0);
Dostaję ten plan, który zwraca za 25 milisekund *, wykonując 358 odczytów, aby zwrócić 72 godzinne sumy:
* Czas trwania mierzony przez nasz darmowy SQL Sentry Plan Explorer , który odrzuca wyniki, więc nie obejmuje czasu przesyłania danych w sieci, renderowania itp. Jako dodatkowe zastrzeżenie, pracuję dla SQL Sentry.
Oczywiście zajmie to trochę więcej czasu, jeśli zwiększę zasięg - miesiąc danych zajmuje 258 ms, dwa miesiące - 500 ms itd. Równoległość może się uruchomić:
W tym miejscu zaczynasz myśleć o innych, lepszych rozwiązaniach w celu zaspokojenia zapytań dotyczących raportowania, i nie ma to nic wspólnego z tym, jaką strefę czasową wyświetli twój wynik. Nie będę się w to angażował, chcę tylko wykazać, że konwersja strefy czasowej tak naprawdę nie spowoduje, że zapytania związane z raportowaniem będą do niczego więcej ssące, a mogą już być do bani, jeśli otrzymujesz duże zakresy, które nie są obsługiwane przez odpowiednie indeksy. Będę trzymać się małych zakresów dat, aby pokazać, że logika jest poprawna, i pozwolę ci się martwić o to, czy Twoje zapytania raportowe oparte na zakresie będą działać poprawnie, niezależnie od konwersji strefy czasowej.
Okej, teraz potrzebujemy tabel do przechowywania naszych stref czasowych (z przesunięciami, w minutach, ponieważ nie wszyscy są nawet godziny wolne od UTC) i dat zmian czasu letniego dla każdego obsługiwanego roku. Dla uproszczenia wprowadzę tylko kilka stref czasowych i jeden rok, aby dopasować powyższe dane.
CREATE TABLE dbo.TimeZones
(
TimeZoneID TINYINT NOT NULL PRIMARY KEY,
Name VARCHAR(9) NOT NULL,
Offset SMALLINT NOT NULL, -- minutes
DSTName VARCHAR(9) NOT NULL,
DSTOffset SMALLINT NOT NULL -- minutes
);
Uwzględniono kilka stref czasowych dla odmiany, niektóre z przesunięciem o pół godziny, niektóre nie przestrzegają czasu letniego. Zauważ, że Australia na półkuli południowej obserwuje DST podczas naszej zimy, więc ich zegary cofają się w kwietniu i przesuwają w październiku. (Powyższa tabela zmienia nazwy, ale nie jestem pewien, jak sprawić, by było to mniej skomplikowane w strefach czasowych półkuli południowej).
INSERT dbo.TimeZones VALUES
(1, 'UTC', 0, 'UTC', 0),
(2, 'GMT', 0, 'BST', 60),
-- London = UTC in winter, +1 in summer
(3, 'EST', -300, 'EDT', -240),
-- East coast US (-5 h in winter, -4 in summer)
(4, 'ACDT', 630, 'ACST', 570),
-- Adelaide (Australia) +10.5 h Oct - Apr, +9.5 Apr - Oct
(5, 'ACST', 570, 'ACST', 570);
-- Darwin (Australia) +9.5 h year round
Teraz tabela kalendarza, aby wiedzieć, kiedy zmieniają się TZ. Zamierzam tylko wstawić wiersze zainteresowania (każda strefa czasowa powyżej i tylko zmiany czasu letniego w 2014 r.). Dla ułatwienia obliczeń tam i z powrotem przechowuję zarówno moment w UTC, w którym zmienia się strefa czasowa, jak i ten sam moment w czasie lokalnym. W strefach czasowych, w których nie obserwuje się czasu letniego, jest to standard przez cały rok, a czas letni rozpoczyna się 1 stycznia.
CREATE TABLE dbo.Calendar
(
TimeZoneID TINYINT NOT NULL FOREIGN KEY
REFERENCES dbo.TimeZones(TimeZoneID),
[Year] SMALLDATETIME NOT NULL,
UTCDSTStart SMALLDATETIME NOT NULL,
UTCDSTEnd SMALLDATETIME NOT NULL,
LocalDSTStart SMALLDATETIME NOT NULL,
LocalDSTEnd SMALLDATETIME NOT NULL,
PRIMARY KEY (TimeZoneID, [Year])
);
Zdecydowanie możesz wypełnić to algorytmami (a nadchodząca seria końcówek używa sprytnych technik opartych na zestawie, jeśli sam to powiem), zamiast zapętlać, ręcznie wypełniać, co masz. W przypadku tej odpowiedzi postanowiłem ręcznie wypełnić jeden rok dla pięciu stref czasowych i nie zamierzam zawracać sobie głowy żadnymi fantazyjnymi sztuczkami.
INSERT dbo.Calendar VALUES
(1, '20140101', '20140101 00:00','20150101 00:00','20140101 00:00','20150101 00:00'),
(2, '20140101', '20140330 01:00','20141026 00:00','20140330 02:00','20141026 01:00'),
(3, '20140101', '20140309 07:00','20141102 06:00','20140309 03:00','20141102 01:00'),
(4, '20140101', '20140405 16:30','20141004 16:30','20140406 03:00','20141005 02:00'),
(5, '20140101', '20140101 00:00','20150101 00:00','20140101 00:00','20150101 00:00');
Okej, więc mamy nasze dane faktów i nasze tabele „wymiarów” (przewracam się, kiedy to mówię), więc jaka jest logika? Zakładam, że użytkownicy wybiorą strefę czasową i wprowadzą zakres dat dla zapytania. Zakładam również, że zakres dat będzie obejmował pełne dni we własnej strefie czasowej; bez częściowych dni, nie wspominając o niepełnych godzinach. Przekażą więc datę początkową, końcową i TimeZoneID. Następnie użyjemy funkcji skalarnej do konwersji daty początkowej / końcowej z tej strefy czasowej na UTC, co pozwoli nam filtrować dane na podstawie zakresu UTC. Po wykonaniu tej czynności i przeprowadzeniu na niej naszych agregacji, możemy zastosować konwersję zgrupowanych czasów z powrotem do źródłowej strefy czasowej, przed wyświetleniem ich użytkownikowi.
Skalarny UDF:
CREATE FUNCTION dbo.ConvertToUTC
(
@Source SMALLDATETIME,
@SourceTZ TINYINT
)
RETURNS SMALLDATETIME
WITH SCHEMABINDING
AS
BEGIN
RETURN
(
SELECT DATEADD(MINUTE, -CASE
WHEN @Source >= src.LocalDSTStart
AND @Source < src.LocalDSTEnd THEN t.DSTOffset
WHEN @Source >= DATEADD(HOUR,-1,src.LocalDSTStart)
AND @Source < src.LocalDSTStart THEN NULL
ELSE t.Offset END, @Source)
FROM dbo.Calendar AS src
INNER JOIN dbo.TimeZones AS t
ON src.TimeZoneID = t.TimeZoneID
WHERE src.TimeZoneID = @SourceTZ
AND t.TimeZoneID = @SourceTZ
AND DATEADD(MINUTE,t.Offset,@Source) >= src.[Year]
AND DATEADD(MINUTE,t.Offset,@Source) < DATEADD(YEAR, 1, src.[Year])
);
END
GO
I funkcja wyceniana w tabeli:
CREATE FUNCTION dbo.ConvertFromUTC
(
@Source SMALLDATETIME,
@SourceTZ TINYINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT
[Target] = DATEADD(MINUTE, CASE
WHEN @Source >= trg.UTCDSTStart
AND @Source < trg.UTCDSTEnd THEN tz.DSTOffset
ELSE tz.Offset END, @Source)
FROM dbo.Calendar AS trg
INNER JOIN dbo.TimeZones AS tz
ON trg.TimeZoneID = tz.TimeZoneID
WHERE trg.TimeZoneID = @SourceTZ
AND tz.TimeZoneID = @SourceTZ
AND @Source >= trg.[Year]
AND @Source < DATEADD(YEAR, 1, trg.[Year])
);
I procedura, która go używa ( edycja : zaktualizowano, aby obsługiwał grupowanie przesunięć o 30 minut):
CREATE PROCEDURE dbo.ReportOnDateRange
@Start SMALLDATETIME, -- whole dates only please!
@End SMALLDATETIME, -- whole dates only please!
@TimeZoneID TINYINT
AS
BEGIN
SET NOCOUNT ON;
SELECT @Start = dbo.ConvertToUTC(@Start, @TimeZoneID),
@End = dbo.ConvertToUTC(@End, @TimeZoneID);
;WITH x(t,c) AS
(
SELECT DATEDIFF(MINUTE, @Start, EventTime_UTC)/60,
COUNT(*)
FROM dbo.Fact
WHERE EventTime_UTC >= @Start
AND EventTime_UTC < DATEADD(DAY, 1, @End)
GROUP BY DATEDIFF(MINUTE, @Start, EventTime_UTC)/60
)
SELECT
UTC = DATEADD(MINUTE, x.t*60, @Start),
[Local] = y.[Target],
[RowCount] = x.c
FROM x OUTER APPLY
dbo.ConvertFromUTC(DATEADD(MINUTE, x.t*60, @Start), @TimeZoneID) AS y
ORDER BY UTC;
END
GO
(Możesz spróbować przejść do zwarcia lub osobnej procedury składowanej w przypadku, gdy użytkownik chce zgłaszać się w UTC - oczywiście tłumaczenie na i z UTC będzie marnowaniem czasu.)
Przykładowe połączenie:
EXEC dbo.ReportOnDateRange
@Start = '20140308',
@End = '20140311',
@TimeZoneID = 3;
Zwraca za 41 ms * i generuje ten plan:
* Ponownie, z odrzuconymi wynikami.
Przez 2 miesiące zwraca za 507 ms, a plan jest identyczny z wyjątkiem liczby wierszy:
Chociaż nieco bardziej skomplikowany i nieco wydłużający się czas działania, jestem całkiem pewien, że tego rodzaju podejście zadziała o wiele, wiele lepiej niż podejście oparte na tabeli brydża. Jest to przykładowy przykład odpowiedzi dba.se; Jestem pewien, że moją logikę i wydajność mogliby poprawić ludzie mądrzejsi ode mnie.
Możesz przejrzeć dane, aby zobaczyć przypadki brzegowe, o których mówię - brak wiersza wyników dla godziny, w której zegary przesuwają się do przodu, dwa rzędy dla godziny, w której cofały się (i ta godzina zdarzyła się dwa razy). Możesz także grać ze złymi wartościami; na przykład, jeśli zdasz 20140309 02:30 czasu wschodniego, nie zadziała to zbyt dobrze.
Mogę nie mieć wszystkich założeń dotyczących tego, jak będzie działało Twoje raportowanie, więc może być konieczne wprowadzenie pewnych korekt. Ale myślę, że obejmuje to podstawy.