SQL Unix 時間戳程式碼片段
SQL 範例,用於在 MySQL、PostgreSQL 和 SQLite 中處理 Unix 時間戳,包括當前紀元值、轉換為日期,以及轉換回時間戳。
MySQL — current timestamp
-- Seconds
UNIX_TIMESTAMP()
-- Milliseconds (MySQL 8.0+)
ROUND(UNIX_TIMESTAMP(NOW(3)) * 1000)MySQL — timestamp to datetime
FROM_UNIXTIME(1700000000)
FROM_UNIXTIME(1700000000, '%Y-%m-%d %H:%i:%S')MySQL — datetime to timestamp
UNIX_TIMESTAMP('2023-11-15 06:13:20')PostgreSQL — current timestamp
-- Seconds (integer)
EXTRACT(EPOCH FROM NOW())::BIGINT
-- Milliseconds
ROUND(EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINTPostgreSQL — timestamp to timestamptz
TO_TIMESTAMP(1700000000)
TO_TIMESTAMP(1700000000) AT TIME ZONE 'America/New_York'PostgreSQL — datetime to epoch
EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2023-11-15T06:13:20Z')::BIGINTSQLite — current timestamp
strftime('%s', 'now') -- secondsSQLite — timestamp to datetime
datetime(1700000000, 'unixepoch')
datetime(1700000000, 'unixepoch', 'localtime')SQL 時間戳記基礎
每個資料庫都提供名稱略有不同的 Unix 時間戳記輔助函式。MySQL 使用 UNIX_TIMESTAMP()，PostgreSQL 可以從 now() 提取紀元秒，SQLite 通常使用 strftime('%s', 'now')。
資料庫轉換說明
當你需要快速的數值比較時，把時間戳記存為整數；當你需要資料庫級的時區和日期函式時，使用原生時間戳記型別。在匯入和匯出邊界明確使用 UTC。
SQL 生產注意事項
資料庫時間戳記的選擇應與該值將如何被查詢相符。整數 Unix 秒對日誌視窗和範圍掃描很簡單。當資料庫必須按日曆日分組、截斷到月或套用帶時區的函式時，原生時間戳記型別更好。不要在沒有明確轉換規則的情況下混用兩種表示。
- 儲存毫秒時間戳記時用 BIGINT 而非 INT
- 對匯入的時間戳記使用 UTC，使應用伺服器在邊界上達成一致
- 使用半開區間，如 created_at >= start and created_at < end
- 對範圍過濾中所用的那一欄建立索引，無論它是紀元秒還是時間戳記型別
SQL 中的 datetime 轉換：GETDATE、CONVERT、CAST
搜尋「datetime convert in sql」「getdate sql」「sql conversion date」「sql getdate date」或「convert date timestamp to date」的 SQL Server 使用者通常想要 T-SQL 輔助函式 GETDATE()、CONVERT() 和 CAST()。MySQL 和 PostgreSQL 使用不同的內建函式，但操作相同：把 Unix 整數變成 datetime，或把 datetime 變成對應的 Unix 整數。
- SQL Server GETDATE() — 伺服器本地時區的目前 datetime；UTC 用 SYSUTCDATETIME()
- SQL Server epoch → datetime: DATEADD(SECOND, 1700000000, '1970-01-01T00:00:00')
- SQL Server datetime → epoch: DATEDIFF_BIG(SECOND, '1970-01-01', GETUTCDATE())
- SQL Server CONVERT/CAST date↔datetime: CONVERT(date, GETDATE()) 去掉時間；CAST('2024-03-15T14:30:00' AS datetime2) 解析 ISO 8601
- MySQL: SELECT UNIX_TIMESTAMP(NOW()); 目前紀元；反向用 FROM_UNIXTIME(1700000000)
- PostgreSQL: SELECT EXTRACT(EPOCH FROM now()); 目前紀元；反向用 to_timestamp(1700000000)
- SQLite: SELECT strftime('%s','now'); 目前紀元；反向用 datetime(1700000000, 'unixepoch')
- SQL CAST AS DATE / sql cast as date — CAST(GETDATE() AS date) 去掉時間部分，只返回 YYYY-MM-DD
- SQL CAST datetime to date / sql cast datetime to date — CAST(your_datetime AS date) 只保留日曆日期
- SQL CONVERT datetime / sql convert datetime — CONVERT(varchar, GETDATE(), 120) 返回 "YYYY-MM-DD HH:MI:SS"；樣式 126 返回 ISO 8601
- SQL format date convert / sql format date convert — SQL Server：自訂版面用 FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss')，ISO 8601 用 CONVERT(varchar, GETDATE(), 126)
- SQL CAST AS integer / sql cast as integer — CAST(value AS int) — 對存於數值欄的紀元值有用
- SQL convert from datetime to date / sql convert from datetime to date — CAST(dt AS date) 或 CONVERT(date, dt) 去掉時間部分
- SQL convert integer / sql convert integer — 對溢位 INT 的 13 位毫秒時間戳記用 CAST(value AS bigint)
- SQL datetime to date / sql datetime to date — CAST(your_datetime AS date) 只返回 YYYY-MM-DD
FAQ
- 應該把紀元毫秒存進 INT 嗎？
- 不。現代毫秒時間戳記是 13 位，無法安全地放進 32 位 INT。請使用 BIGINT 或原生時間戳記型別。
- 資料庫時間戳記總是 UTC 嗎？
- 不。行為取決於資料庫型別和工作階段時區。在跨系統匯入、匯出和比較值時，請明確使用 UTC。