PLSQL：如何将带毫秒的Unix时间戳转换为TIMESTAMP(6)

嘿，这个需求我之前刚好折腾过，其实不同数据库的最优实现略有差异，我给你整理几种主流数据库的解决方案——都是用原生函数，既高效又能避免手动计算带来的闰年、时区这类坑：

Oracle 实现 #

Oracle 的 TIMESTAMP(6) 支持微秒精度，我们先把毫秒时间戳转成秒级带小数的数值，再基于Unix纪元时间（1970-01-01）做计算：

-- 转换为 TIMESTAMP(6) 类型
SELECT CAST(
    TO_TIMESTAMP('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(1523572200000/1000, 'SECOND')
    AS TIMESTAMP(6)
) AS converted_timestamp
FROM DUAL;

-- 如果需要转成你示例中的字符串格式（注意示例是9位小数，Oracle 用 FF9 表示纳秒位）
SELECT TO_CHAR(
    TO_TIMESTAMP('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(1523572200000/1000, 'SECOND'),
    'DD-MON-RR HH.MI.SS.FF9 AM'
) AS formatted_timestamp
FROM DUAL;

MySQL 实现 #

MySQL 的 FROM_UNIXTIME 支持直接传入秒级带小数的数值，配合 CAST 可以直接转成 TIMESTAMP(6)：

-- 转换为 TIMESTAMP(6) 类型
SELECT CAST(FROM_UNIXTIME(1523572200000/1000) AS TIMESTAMP(6)) AS converted_timestamp;

-- 转成指定字符串格式（%f 对应6位微秒，要9位的话可以拼接三个零）
SELECT DATE_FORMAT(
    CAST(FROM_UNIXTIME(1523572200000/1000) AS TIMESTAMP(6)),
    '%d-%b-%y %h.%i.%s.%f %p'
) AS formatted_timestamp;
-- 如果要9位小数：
SELECT CONCAT(
    DATE_FORMAT(CAST(FROM_UNIXTIME(1523572200000/1000) AS TIMESTAMP(6)), '%d-%b-%y %h.%i.%s.%f'),
    '000 ',
    DATE_FORMAT(CAST(FROM_UNIXTIME(1523572200000/1000) AS TIMESTAMP(6)), '%p')
) AS formatted_timestamp_9digits;

PostgreSQL 实现 #

PostgreSQL 的 TO_TIMESTAMP 直接支持秒级带小数的输入，转成 TIMESTAMP(6) 只需加类型转换：

-- 转换为 TIMESTAMP(6) 类型
SELECT TO_TIMESTAMP(1523572200000/1000)::TIMESTAMP(6) AS converted_timestamp;

-- 转成指定字符串格式（US 对应6位微秒，要9位就拼接零）
SELECT TO_CHAR(
    TO_TIMESTAMP(1523572200000/1000)::TIMESTAMP(6),
    'DD-Mon-RR HH.MI.SS.US AM'
) AS formatted_timestamp;
-- 9位小数版本：
SELECT CONCAT(
    TO_CHAR(TO_TIMESTAMP(1523572200000/1000)::TIMESTAMP(6), 'DD-Mon-RR HH.MI.SS.US'),
    '000 AM'
) AS formatted_timestamp_9digits;

小提示 #

如果你的业务需要考虑时区，记得在转换时指定对应的时区（比如 Oracle 用 FROM_TZ，PostgreSQL 用 AT TIME ZONE），避免因为服务器时区差异导致转换结果出错。

内容的提问来源于stack exchange，提问作者dukenukem6487