用於報告多個時區數據的數據倉庫設計
我們正在嘗試優化數據倉庫設計，該設計將支持針對多個時區的數據進行報告。例如，我們可能有一個月的活動報告（數百萬行），需要顯示按一天中的小時分組的活動。當然，一天中的那個小時必須是給定時區的“本地”小時。
當我們只支持 UTC 和一個本地時間時，我們的設計執行良好。UTC 和本地時間的日期和時間維度的標准設計，事實表上的 id。但是，如果我們必須支持 100 多個時區的報告，這種方法似乎無法擴展。
我們的事實表會變得很寬。此外，我們必須解決 SQL 中的語法問題，即指定在任何給定的報告執行中使用哪個日期和時間 ID 進行分組。也許是一個非常大的 CASE 語句？
我已經看到了一些建議，可以按您所覆蓋的 UTC 時間範圍獲取所有數據，然後將其返回到表示層以轉換為本地並在那裡聚合，但是使用 SSRS 進行的有限測試表明這將非常緩慢。
我也查閱了一些關於這個主題的書，他們似乎都說只有 UTC 並轉換顯示，或者有 UTC 和一個本地的。將不勝感激任何想法和建議。
注意：這個問題類似於：Handling time zone in data mar/warehouse，但我不能對這個問題發表評論，所以覺得這值得自己提出問題。
**更新：**在 Aaron 進行了一些重要更新並發布了範常式式碼和圖表後，我選擇了他的答案。我之前對他的答案的評論將不再有意義，因為他們提到了答案的原始編輯。如果有必要，我會嘗試回來並再次更新
我已經通過一個非常簡單的日曆表解決了這個問題 - 每年每個支持的時區都有一行，具有標準偏移量和 DST 的開始日期時間/結束日期時間及其偏移量（如果該時區支持它）。然後是一個內聯的、模式綁定的、表值函式，它採用源時間（當然是 UTC）並添加/減去偏移量。
如果您針對大量數據進行報告，這顯然永遠不會表現得非常好；分區似乎有幫助，但是當轉換為特定時區時，您仍然會遇到一年中的最後幾個小時或明年的前幾個小時實際上屬於不同年份的情況 - 因此您永遠無法獲得真正的分區隔離，除非您的報告範圍不包括 12 月 31 日或 1 月 1 日。
您需要考慮幾個奇怪的邊緣情況：
- 例如，2014-11-02 05:30 UTC 和 2014-11-02 06:30 UTC 都轉換為東部時區的 01:30 AM（一個是第一次在本地擊中 01:30，然後是一個第二次時鐘從凌晨 2:00 回到凌晨 1:00，又過了半小時）。因此，您需要決定如何處理該小時的報告 - 根據 UTC，一旦將這兩個小時映射到遵守 DST 的時區中的一個小時，您應該會看到您正在測量的任何內容的流量或量的兩倍。這也可以通過事件順序來玩有趣的遊戲，因為在邏輯上必須在其他事情出現之後發生的事情一旦時間調整為一個小時而不是兩個小時，就會在它之前發生。一個極端的例子是在 05:59 UTC 發生的頁面瀏覽，然後在 06:00 UTC 發生的點擊。在 UTC 時間中，這些時間相隔一分鐘，但當轉換為東部時間時，視圖發生在凌晨 1:59，點擊發生在一個小時前。
- 2014-03-09 02:30 在美國從未發生過。這是因為在凌晨 2:00，我們將時鐘向前滾動到凌晨 3:00。如果使用者輸入這樣的時間並要求您將其轉換為 UTC，或者設計您的表單以使使用者無法選擇這樣的時間，那麼您很可能會想要引發錯誤。
即使考慮到這些邊緣情況，我仍然認為您有正確的方法：將數據儲存在 UTC 中。將數據從 UTC 映射到其他時區比從某個時區映射到其他時區要容易得多，特別是當不同時區在不同日期開始/結束 DST 時，甚至同一時區可以在不同年份使用不同規則切換（例如，美國在 6 年前左右更改了規則）。
您將希望為所有這些使用日曆表，而不是一些龐大的
CASE表達式（不是語句）。我剛剛為MSSQLTips.com寫了一個由三部分組成的系列文章；我認為第三部分對你最有用：
- 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/
與此同時，一個真實的例子
假設您有一個非常簡單的事實表。在這種情況下，我關心的唯一事實是事件時間，但我將添加一個無意義的 GUID 只是為了使表格足夠寬以關心。同樣，明確地說，事實表僅以 UTC 時間和 UTC 時間儲存事件。我什至在列後綴，
_UTC所以沒有混淆。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現在，讓我們用 10,000,000 行載入我們的事實表 - 表示從 2013 年 12 月 30 日午夜 UTC 到 2014 年 12 月 12 日凌晨 5 點之後的某個時間，每 3 秒（每小時 1,200 行）。這可確保數據跨越年份邊界，以及多個時區的 DST 前後。這看起來真的很可怕，但在我的系統上花了大約 9 秒。表格最終應該是大約 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;如果我執行這個查詢，只是為了展示一個典型的查找查詢對於這個 10MM 行表的樣子：
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);我得到了這個計劃，它在 25 毫秒* 內返回，進行 358 次讀取，返回 72 小時總計：
*持續時間由免費的SentryOne Plan Explorer測量，它會丟棄結果，因此這不包括數據的網路傳輸時間、渲染等。
顯然，如果我的範圍太大，則需要更長的時間——一個月的數據需要 258 毫秒，兩個月需要超過 500 毫秒，依此類推。並行性可能會起作用：
這是您開始考慮其他更好的解決方案以滿足報告查詢的地方，它與您的輸出將顯示的時區無關。我不會深入討論，我只是想證明時區轉換並不會真正讓您的報告查詢變得更糟糕，如果您獲得的範圍很大而不受適當的支持，它們可能已經很糟糕索引。我將堅持使用較小的日期範圍來表明邏輯是正確的，並讓您擔心確保基於範圍的報告查詢能夠充分執行，無論是否有時區轉換。
好的，現在我們需要表格來儲存我們的時區（帶有偏移量，以分鐘為單位，因為不是每個人都比 UTC 時間差幾個小時）和每個支持年份的 DST 更改日期。為簡單起見，我將只輸入幾個時區和一年來匹配上面的數據。
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 );包括幾個不同的時區，一些有半小時偏移，一些不遵守 DST。請注意，位於南半球的澳大利亞在我們的冬季觀察夏令時，因此他們的時鐘在 4 月倒退，在 10 月**前進。（上表顛倒了名稱，但我不知道如何讓南半球時區更容易混淆。）
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現在，一個日曆表可以知道 TZ 何時更改。我只會插入感興趣的行（上面的每個時區，只有 2014 年的 DST 更改）。為了便於來回計算，我將時區更改的 UTC 時刻和本地時間的同一時刻都儲存起來。對於不遵守 DST 的時區，它是全年的標準時間，並且 DST 於 1 月 1 日“開始”。
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]) );你絕對可以用算法填充它（如果我自己這麼說的話，即將到來的技巧系列使用一些基於集合的巧妙技術），而不是循環，手動填充，你有什麼。對於這個答案，我決定為五個時區手動填充一年，我不會打擾任何花哨的技巧。
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');好的，所以我們有我們的事實數據和我們的“維度”表（當我這麼說時我畏縮了），那麼邏輯是什麼？好吧，我假設您將讓使用者選擇他們的時區並輸入查詢的日期範圍。我還將假設日期範圍將是他們自己時區的一整天；沒有部分的日子，更別提部分的時間了。所以他們會傳入開始日期、結束日期和 TimeZoneID。從那裡我們將使用標量函式將該時區的開始/結束日期轉換為 UTC，這將允許我們根據 UTC 範圍過濾數據。完成此操作並對其執行聚合後，我們可以將分組時間的轉換應用回源時區，然後再顯示給使用者。
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和表值函式：
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]) );以及使用它的過程（編輯：更新以處理 30 分鐘偏移分組）：
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（如果使用者想要在 UTC 中報告，您可能希望在那裡進行短路或單獨的儲存過程 - 顯然與 UTC 之間的轉換將是浪費的忙碌工作。）
範例呼叫：
EXEC dbo.ReportOnDateRange @Start = '20140308', @End = '20140311', @TimeZoneID = 3;在 41ms* 內返回，並生成此計劃：
*同樣，丟棄結果。
2 個月，它在 507 毫秒內返回，除了行數之外，計劃是相同的：
雖然稍微複雜一些並且執行時間增加了一點，但我相當有信心這種方法會比橋接表方法好得多。這是 dba.se 答案的現成範例；我確信比我聰明得多的人可以提高我的邏輯和效率。
您可以仔細閱讀數據以查看我談論的邊緣情況 - 時鐘前滾的小時沒有輸出行，它們回滾的小時有兩行（並且那個小時發生了兩次）。你也可以玩壞價值觀；例如，如果你在東部時間 20140309 02:30 通過，它就不會工作得很好。
對於您的報告將如何運作，我可能沒有正確的所有假設，因此您可能需要進行一些調整。但我認為這涵蓋了基礎知識。
