PostgreSQL jsonb列UNIX时间戳转可读时间戳问题求助

嘿，看来你已经搞定了jsonb数组解析成行的步骤，剩下的时间戳转换其实很简单，PostgreSQL自带的函数就能轻松搞定！

核心方法：用to_timestamp()函数转换 #

PostgreSQL的to_timestamp()函数可以直接把UNIX秒级时间戳（就是你例子里的1521505355这种整数）转换成可读的时间格式，默认会返回带时区的timestamp with time zone类型，完全满足日常需求。

结合你的jsonb结构的完整查询示例 #

假设你的表名叫signal_data，存储jsonb的列名叫payload，那你可以这么写查询：

SELECT
  -- 提取jsonb里的其他字段
  (elem->>'id') AS signal_id,
  (elem->>'on')::boolean AS is_signal_on,
  elem->>'unit' AS signal_unit,
  -- 重点：转换UNIX时间戳
  to_timestamp((elem->>'timestamp')::bigint) AS readable_timestamp
FROM signal_data,
     -- 这里是你已经用到的解析jsonb数组的方法
     jsonb_array_elements(payload->'signal') AS elem;

细节说明： #

• 为什么要加::bigint？因为从jsonb里用->>提取出来的是字符串类型，必须先转换成整数类型，to_timestamp()才能正确识别它是时间戳。
• 如果想要不带时区的时间格式，只需要把结果再转成timestamp类型就行：

  to_timestamp((elem->>'timestamp')::bigint)::timestamp AS readable_timestamp_no_tz
  

• 万一遇到毫秒级的UNIX时间戳（比如1521505355123这种），只需要把数值除以1000.0就行：

  to_timestamp((elem->>'timestamp')::bigint / 1000.0) AS readable_timestamp
  

容错处理（可选） #

如果你的jsonb数据里可能存在timestamp字段缺失或者不是数字的情况，可以用COALESCE来避免报错：

to_timestamp(COALESCE((elem->>'timestamp')::bigint, 0)) AS readable_timestamp

这样当timestamp字段无效时，会返回1970-01-01 00:00:00这个默认时间，你也可以换成自己需要的默认值。

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