如何在PostgreSQL中以可读格式计算两个时间戳的差值
2026-7-9
PostgreSQL计算Unix毫秒时间戳差值并格式化可读时长
我在PostgreSQL中有一张表，包含created_on和closed_on两列，存储的是Unix毫秒时间戳。需要计算这两个时间戳的差值，并以可读格式展示，例如1 year, 9 days, 1 hour, 6 minutes, 1 second。
初始表结构及数据
| id | created_on | closed_on |
|---|---|---|
| 1 | 1654850834025 | 1687168395586 |
尝试的查询语句
SELECT (closed_on - created_on) * interval '1 millisecond' as interval1, justify_interval(32317561561 * interval '1 millisecond') as interval2, to_timestamp(closed_on / 1000)::timestamp - to_timestamp(created_on / 1000)::timestamp as interval3, to_timestamp(created_on / 1000)::timestamp as created_on, to_timestamp(closed_on / 1000)::timestamp as closed_on FROM test
查询返回结果
| interval1 | interval2 | interval3 | created_on | closed_on |
|---|---|---|---|---|
| {"hours":8977,"minutes":6,"seconds":1,"milliseconds":561} | {"years":1,"days":14,"hours":1,"minutes":6,"seconds":1,"milliseconds":561} | {"days":374,"hours":1,"minutes":6,"seconds":1} | 2022-06-10T08:47:14.000Z | 2023-06-19T09:53:15.000Z |
存在的问题
- interval1仅显示小时，未展示天、月等单位
- interval2错误显示14天，实际应为1年零9天
- interval3天数正确，但未展示年、月单位
期望结果
1 year, 9 days, 1 hour, 6 minutes, 1 second
内容的提问来源于stack exchange，提问作者executable
相关产品推荐
相关产品推荐
