在資料庫中儲存時間戳:DATETIME vs INT vs BIGINT

儲存時間戳的三種方式

大多數資料庫至少提供三種選擇:原生 datetime 型別(TIMESTAMP、DATETIME、TIMESTAMPTZ)、普通整數(INT 或 BIGINT),或字串(VARCHAR)。它們在儲存大小、查詢易用性、時區處理和面向未來方面各有取捨。對大多數產品資料庫,原生 datetime 欄位是最佳預設,因為資料庫可以把該值當作時間(而非匿名數字)來比較、索引、截斷、分組和格式化。

• 原生 datetime 型別 —— 最適合日期算術、時區轉換和可讀性
• BIGINT 整數 —— 適合高吞吐插入和簡單的數值範圍查詢
• VARCHAR 字串 —— 幾乎總是錯的:日期的字串比較只對嚴格的 ISO 8601 格式有效
• INT 整數 —— 除非已徹底核查 2038 年邊界,否則對未來時間戳應避免

MySQL:TIMESTAMP vs DATETIME vs INT

MySQL 有兩種看起來相似但行為差異很大的日期時間型別——其中一種有硬性的到期日。當你想要 UTC 與工作階段時區之間自動轉換時,TIMESTAMP 很方便,但它歷史上的 32 位範圍使其對面向未來的產品資料有風險。DATETIME 按你提供的字面日期時間儲存,當應用在寫入前統一使用 UTC 時通常更清晰。

• TIMESTAMP:內部以 32 位 Unix 秒儲存 —— 限於 1970-01-01 至 2038-01-19
• TIMESTAMP:在插入/讀取時自動在 UTC 與工作階段時區之間轉換
• DATETIME:儲存字面日期時間,無時區。範圍 1000-01-01 至 9999-12-31。不受 Y2038 影響。
• DATETIME:不轉換時區 —— 你在應用層控制 UTC
• 建議:新表使用帶顯式 UTC 值的 DATETIME,以避開 2038 年限制

PostgreSQL:TIMESTAMPTZ 是正確選擇

PostgreSQL 的 TIMESTAMP WITH TIME ZONE(TIMESTAMPTZ)在內部以 UTC 微秒儲存時間戳,並在輸出時轉換為工作階段時區。對大多數用例而言,它是最安全、最正確的選擇,因為它表示時間中的真實瞬間。這個名字可能誤導:TIMESTAMPTZ 並不儲存像 America/New_York 這樣的原始時區標籤。它儲存瞬間,然後按當前工作階段時區顯示。

• TIMESTAMPTZ:儲存 UTC,在輸出時轉換為工作階段時區 —— 可移植且對日光節約時間安全
• TIMESTAMP(無時區):按字面值儲存且不轉換 —— 僅用於無時區資料
• EXTRACT(EPOCH FROM col):從任意 TIMESTAMP 欄位以 float 回傳 Unix 秒
• TO_TIMESTAMP(epoch):把 Unix 秒轉換回 TIMESTAMPTZ

索引與查詢效能

對普通應用表,原生 datetime 欄位與 BIGINT epoch 欄位之間的效能差異很少是決定因素。查詢形態、索引設計、分區和列數更重要。先選擇能正確保留含義的型別,再為應用實際執行的範圍查詢為其建立索引。

• 三種型別都支援 B-tree 索引和高效的範圍查詢
• 在超大表的等值和範圍掃描上,BIGINT 整數略快
• 原生 datetime 型別允許按日期部分的索引查詢:WHERE created_at::date = '2024-01-01'
• VARCHAR 時間戳的效能最差 —— 字串比較不識別日期

何時適合用 BIGINT 儲存 epoch

當資料呈事件型、以追加為主、且已由另一個系統產生為 Unix 時間時,BIGINT 是合理的。分析管線、遙測流、佇列和精簡的二進位協定常用 epoch 毫秒,因為數值比較快且與語言無關。代價是可讀性:人需要轉換器,而 SQL 的日期算術會更冗長。

• 如果 JavaScript 用戶端直接產生事件,Unix 毫秒使用 BIGINT
• 如果來源系統是 Unix 風格且秒精度足夠,Unix 秒使用 BIGINT
• 在欄位名中記錄單位:created_at_ms 比 created_at_epoch 更清晰
• 如果分析師需要可讀的 SQL 查詢,加入一個產生的 datetime 欄位
• 因 32 位範圍限制,面向未來的現代時間戳應避免使用 INT

推薦的綱要設計

對大多數 Web 應用,把瞬間以 UTC 儲存,只有在需要重建本地掛鐘意圖時才單獨儲存使用者偏好的時區。安排在 America/New_York 上午 9:00 的會議,不同於在精確 UTC 瞬間建立的事件日誌;請對這些情況分別建模。

• 事件日誌:PostgreSQL 用 created_at TIMESTAMPTZ,MySQL 用 UTC 的 created_at DATETIME
• JavaScript 事件擷取:created_at_ms BIGINT 加上清晰的 API 文件
• 重複的本地排程:儲存 local_date、local_time 和 timezone_id,再計算下一個瞬間
• 過期時間戳:expires_at 用原生 datetime,或 expires_at_seconds 用顯式 Unix 秒
• 稽核表:把 created_at 和 updated_at 都保留為原生 datetime 欄位,以便可讀地偵錯

資料庫時間戳常見問題

資料庫中應存 UTC 還是本地時間?

為事件時間戳儲存 UTC,顯示時轉換為本地時間。當使用者的本地掛鐘意圖重要時(如重複會議或營業時間),單獨儲存一個時區識別碼。

BIGINT 比 TIMESTAMP 更好嗎?

通常不是。BIGINT 對數值型 epoch 管線有用,但原生 datetime 型別在 SQL 日期算術、可讀偵錯和時區感知輸出方面更容易。

MySQL 應使用 TIMESTAMP 還是 DATETIME?

對新的應用表,帶 UTC 值的 DATETIME 通常更安全,因為它避開了 2038 年範圍限制,且不會悄悄依賴工作階段時區轉換。

時間戳應存為 UTC 還是帶時區?

把瞬間以 UTC 儲存(PostgreSQL 用 TIMESTAMPTZ,MySQL 用帶 UTC 值的 DATETIME),顯示時轉換為本地時間。僅當你必須重建使用者的本地掛鐘意圖(如重複會議)時,才保留單獨的 IANA 時區欄位。