PostgreSQL中如何将numeric类型微秒时间戳转为正确的YYYY-MM-DD HH:mm:ss格式日期

问题原因 #

你使用的start_dt_timestamp为微秒级时间戳（共16位，精度为1微秒=1/1000000秒），原有代码仅除以1000转换，相当于误将微秒识别为毫秒，输入数值过大导致日期计算偏移到了五万多年。

正确写法 #

通用SQL（以PostgreSQL为例） #

先将微秒级时间戳除以1000000转为秒级，再进行时间戳转换和格式化：

select 
  start_dt_timestamp,
  -- 转时间戳类型
  to_timestamp(start_dt_timestamp / 1000000) as dt,
  -- 直接输出'YYYY-MM-DD HH:mm:ss'格式的字符串
  to_char(to_timestamp(start_dt_timestamp / 1000000), 'YYYY-MM-DD HH24:MI:SS') as formatted_dt
from tmp_table 

你的示例时间戳1554747217660018转换后得到的正确日期为2019-04-08 21:33:37。

MySQL版本写法 #

如果使用MySQL数据库，可直接用from_unixtime函数完成转换：

select 
  start_dt_timestamp,
  from_unixtime(start_dt_timestamp / 1000000, '%Y-%m-%d %H:%i:%s') as formatted_dt
from tmp_table

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