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。