築夢角落

致力於用最生活化的例子讓所有人都能懂程式,也喜歡分享動漫、小說心得,以及自己的所見所聞、所思所想。

[MySQL]Timestamp跟Datetime的差別,由Timezone決定

最後更新時間 : 2022-10-05 | viewed : 51

datetime vs timestamp

 

這次來聊聊Datetime(日期與時間)、Timestamp(時間戳)、TimeZone(時區)這三個東西之間的關係。

受限於人類定義的時間系統,當我們要與別人討論時間的時候,我們必須先聲明彼此所在的時區

平時,由於我們都生活在同一個時區,自然不需要特別聲明,所以也很容易忽略這點。

但如果我們是和外地的朋友提到時間,由於兩國或兩個地區之間可能有時差,所以就會特別確認雙方說的是哪個時區。

 

 

Datetime(日期與時間)


Datetime 包含日期時間兩個部份,通常以 2022-10-04 18:00:00 這類 yyyy-MM-dd HH:mm:ss 的格式表示,閱讀上較為直覺,也可以照自己喜好替換顯示格式。

有些程式語言的標準庫可能會提供 Datetime 相關的資料類型,若沒有則可以用一般的字串(string)來表示 Datetime。

 

Timestamp(時間戳)


Timestamp 是一個時間戳記,廣義上是指從UTC1970年1月1日0時0分0秒起至現在的總秒數,狹義上可以是任意的、具有時間意義的標記。(本次主要談論前者)

由於讀秒至今仍在進行中,所以 Timestamp 每秒都在變化,通常以一串整數表示。

 

舉例來說:2022-10-04 18:00:00 的 Timestamp 是 1664877600000。(表示自1970年1月1日0時0分0秒起,已經過了 1664877600 秒。)

 

黃色部份是用來表示毫秒,雖然不在標準定義中,但有些程式語言或應用程式會支援(或強制規範)以毫秒顯示,例如 javascript 跟 MySQL。 

(雖然 Timestamp 看起來不好閱讀,但其實像 MySQL Workbench 這類資料庫管理工具,會自動把 Timestamp 的欄位轉成 Datetime 的格式來顯示,解決了不易閱讀的問題。)

 

Datetime 跟 Timestamp 互轉


從上面的說明應該不難看出,Datetime 跟 Timestamp 都是用來表示時間,雖然表示的方式不同,但其結果是等價的,所以也可以相互轉換。

下面是一段 javascript 的示例。

var timestamp = new Date().getTime(); // 取得當前時間的Timestamp。
var timestamp = new Date("2022-10-04 18:00:00").getTime(); // 取得指定時間的Timestamp。

var datetime = new Date(timestamp).toLocaleString(); // 取得指定Timestamp的Datetime。

每個程式語言的寫法雖然不同,但概念是相同的。

順帶一提,在 javascript 裡,如果想自訂 Datetime 的顯示格式,就必須自己用 new Date().getFullYear() 這類函式,依序取得年、月、日、時、分、秒,然後再組成字串。

 

Datetime 跟 Timestamp 的差別


還記得我們前面提到,Datetime 是字串,Timestamp 是1970年至今的總秒數嗎?

如果有一張紙條上寫著 2022-10-04 18:00:00,在台灣看到的人會認為是台灣時間,在日本看到的人會以為是日本時間,在美國看到的人會以為是美國時間......

換句話說,雖然大家看到的時間好像是一樣的,但意義卻完全不同

例如我想跟日本朋友約 18:00 通話,請他教我日文,但是台灣時區的 18:00 是日本時區的 19:00,所以我們勢必會錯過。

如果換成 Timestamp 就不會有這個問題,因為對任何時區的人來說,1970年至今經過的總秒數是相同的。如果我跟日本朋友約在 1664877600000 的時候通話,那我們就會順利在台灣時區 18:00、日本時區 19:00 時開始聊天。

實務上,我們可以想像一個情境。

如果和你(的 Server)互動的所有對象都跟你在同一個時區,那用 Datetime 或 Timestamp 基本上沒有差別。

但如果和你(的 Server)互動的對象都來自不同時區,或是你(的 Server)會搬遷到不同時區,那要用 Datetime 還是 Timestamp,就取決於你希望大家看到的是相同的字面值(例如 2022-10-04 18:00:00),或者是大家都用 Timestamp 轉換成自己時區的時間

 

MySQL 的時間欄位與系統時區


MySQL 的時間類型也有 datetime 跟 timestamp 兩種,要使用哪一種資料類型,可以參考上面的思路。

這邊要特別提的部份是,MySQL 的設定檔內是有標註時區的,預設值可能是你的系統時區。

我們可以透過執行 SELECT @@global.time_zone, @@session.time_zone; 語句來查看時區的設定值。

由於 datetime 是以保持字面值不變為主,所以當 MySQL 的時區改變時,datetime 顯示的日期與時間也不會改變,改變的會是 timestamp。

而如果欄位類型是 timestamp,由於 timestamp 記錄的是1970年至今的總秒數,無論你在哪個時區,看到的總秒數的值都是一樣的,所以當 MySQL 的時區改變時,timestamp 不會改變,只是在 MySQL 內將 timestamp 轉成 datetime 來顯示時,會因為你當前的系統時區不同而顯示不同結果。

 

總結


下面用兩張圖來總結 Datetime 跟 Timestamp 的轉換問題。

無論是 MySQL 的欄位類型,亦或是 Client-Server 架構的系統,在這部份的概念是一樣的

 

(欄位類型 datetime,在各時區看到的 timestamp。)

 

(欄位類型 timestamp,在各時區看到的 datetime。)

 

如果你指定的是 datetime,那麼各個時區的人看到的都會是相同的字面值(2022-10-04 18:00:00),而當他們想轉換成 timestamp 時,就會得到不同的時間戳記

如果你指定的是 timestamp,那麼各個時區的人看到的都會是相同的時間戳記(1664877600000),而當他們想轉換成 datetime 來顯示時,就會得到自己時區的時間

所以要用 datetime 還是 timestamp,取決於你希望大家看到的是相同的字面值,或是大家都用 Timestamp 轉換成自己時區(或指定時區)的時間來顯示

 

補充


由於 MySQL 的 timestamp 的長度只有 32-bit,所以理論上計算總秒數的極限只到 2038 年,超過就會溢位。

而 datetime 理論上可以表示到 9999-12-31 23:59:59。

雖然這是一個坑,不過就我個人的想法,在思考要用 timestamp 還是 datetime 的時候,可以先不把 2038 年的問題列入考慮。

因為這個問題有可能在未來被解決,無論是直接從根源解決,或是用某些手法避免問題發生。

如果你很確定你的系統會運作到 2038 年,並且又需要使用 timestamp,那也可以考慮使用 MySQL 以外的資料庫,或是未來再遷移資料庫。

 

 

有任何想法歡迎留言討論~

希望有幫助到你!

 

 
我要留言!
 

X
暱稱(選填)
email(選填,僅站長可見。)
留言 To:#