Hits: 4539

[MSSQL] 不同時區的資料處理筆記(UTC/offset/local time)

公司的產品，會收集三個不同時區的收視資料。為了處理跨時區的資料，本篇筆記紀錄不同時間格式(UTC/offset/local time)的處理方式、運算與儲存到MSSQL資料庫的方法。

SQL Server中的時間格式

為了將不同時區的資料標準化，通常有幾種作法

1. 直接將資料存成UTC時間
2. 把時區也一併存入資料中

我們公司的做法是把時區資料一起存入資料庫的作法，因為我們公司是數位媒體業，廣告的業務/服務都是根據當地時間在計算的，因此將帶有時區的資料一起存入後是比較合適的作法。

UTC格式與帶時區的格式(datetimeoffset)

根據維基百科所述：世界協調時間是最主要的世界時間標準，其以原子時秒長為基礎，在時刻上儘量接近於格林威治標準時間（英語：Coordinated Universal Time，法語：Temps Universel Coordonné，簡稱UTC）。

換句話說，也就是+0時區(台灣是+8時區)的意思在SQL Server中，UTC時間的取用方式如下。

SELECT 'GETDATE()          ', GETDATE();   --local date
SELECT 'SYSDATETIMEOFFSET()', SYSDATETIMEOFFSET();   --local date with offset(時區)
SELECT 'GETUTCDATE()       ', GETUTCDATE();  --UTC date

/* Returned:  
GETDATE()                2007-05-03 18:34:11.933  
SYSDATETIMEOFFSET()      2007-05-03 18:34:11.9351421 -07:00
GETUTCDATE()             2007-05-04 01:34:11.933  
*/

datetimeoffset時間格式的運算

若是同一天的datetimeoffset，運算沒問題，@date_new的時間-8小時後大於等於@date_ori的時間

declare @date_ori datetimeoffset
declare @date_new datetimeoffset
set @date_ori = '2019-03-29 10:00:00.0000000 +00:00'
set @date_new = '2019-03-29 18:00:00.0000000 +08:00'
select 
  case
    when @date_ori <= @date_new then 1 else 0
  end [date_compare]

# result
date_compare
1

即便有跨天依然沒問題

declare @date_ori datetimeoffset
declare @date_new datetimeoffset
set @date_ori = '2019-03-29 16:00:00.0000000 +00:00'
set @date_new = '2019-03-30 00:00:00.0000000 +08:00'
select 
  case
    when @date_ori <= @date_new then 1 else 0
  end [date_compare]

# result
date_compare
1