Skip to content
标签
开发/数据/数据库
计算机/数据库/postgres
计算机/数据库/postgresql
开发/语言/SQL
开发/数据/时间处理
开发/数据/数据处理
开发/数据/时区
字数
169 字
阅读时间
1 分钟

时间类型互相转换

timestamp 转换为 UNIX 时间戳

sql
SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP::timestamp) AS unix_timestamp;

timestamp 转换为毫秒级 UNIX 时间戳

sql
SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP::timestamp) * 1000 AS unix_timestamp_milli;

切除 UNIX 时间戳的毫秒部分

sql
SELECT CEIL(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP::timestamp)) AS unix_timestamp;

切除毫秒级 UNIX 时间戳的微秒部分

sql
SELECT CEIL(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP::timestamp) * 1000)::bigint AS unix_timestamp_milli;

UNIX 时间戳转换为 timestamp

sql
SELECT TO_TIMESTAMP(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)) AS timestamp;

切除 timestamp 的毫秒部分

sql
SELECT DATE_TRUNC('second', TO_TIMESTAMP(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP))) AS timestamp;

时区转换

偏移时区

sql
SELECT CURRENT_TIMESTAMP::timestamp AT TIME ZONE 'UTC+08' AS now_china_timstamp

指定时区

sql
SELECT CURRENT_TIMESTAMP::timestamp AT TIME ZONE 'Asia/Shanghai' AS now_china_timstamp

贡献者

文件历史

撰写