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

點閱: 919

[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

About the Author

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

Related Posts