时间戳转换是什么
UNIX_TIMESTAMP 把日期时间换算成 1970-01-01 00:00:00 UTC 起的秒数，FROM_UNIXTIME 反向还原，这两个是 MySQL 的函数。PostgreSQL 没有同名函数，用 EXTRACT(EPOCH FROM ts) 取秒数、to_timestamp(秒) 还原。跨库最容易踩的是秒与毫秒的位数，以及显示时区。
基本信息
| MySQL 正向 | UNIX_TIMESTAMP([datetime])，省略参数返回当前时间 |
|---|---|
| MySQL 反向 | FROM_UNIXTIME(秒[, 格式]) |
| PostgreSQL 正向 | EXTRACT(EPOCH FROM now())::bigint |
| PostgreSQL 反向 | to_timestamp(秒)，返回带时区的时间戳 |
| 单位 | 两库默认都是秒级；13 位毫秒时间戳要先除以 1000 |
| 时区 | MySQL 按会话 time_zone 折算；PG 的 to_timestamp 返回 timestamptz |
| 范围 | MySQL 的 TIMESTAMP 类型上限是 2038-01-19，DATETIME 无此限制 |
MySQL 双向转换
MySQL 双向转换
SELECT UNIX_TIMESTAMP(); -- 当前时间的秒级时间戳
SELECT UNIX_TIMESTAMP('2024-06-01 00:00:00'); -- 指定时间转成秒
SELECT FROM_UNIXTIME(1717171200); -- 秒转回日期时间
SELECT FROM_UNIXTIME(1717171200, '%Y-%m-%d'); -- 转回后直接格式化
SELECT TIMESTAMPDIFF(SECOND, '2024-01-01 00:00:00', NOW()); -- 任意两点相差秒数PostgreSQL 对照写法
PostgreSQL 对照写法
SELECT EXTRACT(EPOCH FROM now())::bigint; -- 当前秒级时间戳
SELECT to_timestamp(1717171200); -- 得到 timestamptz
SELECT to_timestamp(1717171200) AT TIME ZONE 'UTC'; -- 换成 UTC 墙上时间
SELECT to_timestamp(1717171200)::date; -- 只取日期部分
SELECT to_timestamp(1717171200.5); -- 支持小数，可保留毫秒两库对照
| 需求 | MySQL 8 | PostgreSQL |
|---|---|---|
| 当前秒数 | UNIX_TIMESTAMP() | EXTRACT(EPOCH FROM now()) |
| 日期转秒 | UNIX_TIMESTAMP(dt) | EXTRACT(EPOCH FROM ts) |
| 秒转日期 | FROM_UNIXTIME(n) | to_timestamp(n) |
| 毫秒时间戳 | FROM_UNIXTIME(n/1000) | to_timestamp(n/1000.0) |
| 格式化输出 | DATE_FORMAT(dt,'%Y-%m-%d') | to_char(ts,'YYYY-MM-DD') |
| 取整秒 | FLOOR(UNIX_TIMESTAMP(dt)) | EXTRACT(EPOCH FROM ts)::bigint |
典型使用场景
- 日志与业务表关联：日志存时间戳、业务表存日期时间
- 计算两点间隔秒数，用于耗时统计与超时判定
- 数据导入：把外部系统给的秒数或毫秒数转成日期列
- 分区分桶：整数比较通常比日期比较更省
- 缓存与令牌过期时间以整数秒存储
- 排查时间显示异常：先确认存的到底是秒级还是毫秒级
- 对比两个系统的同一事件时间：先换算到同一单位再比较
- 把埋点上报的时间戳还原成可读日期，核对上报是否延迟
毫秒时间戳是 13 位，秒级是 10 位。判断位数最简单：长度大于 10 就按毫秒处理，先除以 1000，否则会得到一个年份离谱的日期，而且是静默出错。
存时间戳还是存日期时间属于设计期选择：整数时间戳便于范围比较与排序，且不受会话时区影响；日期时间的可读性更好、便于人工排查。两者都用时，务必在字段注释里写清单位与时区。
MySQL 的 TIMESTAMP 列有 2038-01-19 的上限，超出直接溢出；合同到期、证书有效期这类远期时间用 DATETIME。另外 TIMESTAMP 会随会话时区换算、DATETIME 不会，同表混用会让对账口径不一致。