PostgreSQL使用age函数计算两个时间戳差值未向上进位问题

你看到的date_started和date_completed的整秒值是客户端展示时截断/四舍五入后的结果，两个字段实际存储了亚秒级的时间精度：

• PostgreSQL的timestamp without timezone类型默认支持微秒级精度，字段值可以存储到秒后6位小数，比如2021-11-12 09:11:00.325121
• 大部分数据库客户端默认展示timestamp时会隐藏亚秒部分，你看到的12/11/2021 09:11:00实际是省略小数后的展示值，不是字段的完整存储值

举个实际场景：
如果两个字段的完整存储值为：

• date_started = '2021-11-12 09:11:00.250000'
• date_completed = '2021-11-12 09:19:00.249999'
  两者的差值为7分钟59.999999秒，AGE()函数返回的interval就是00:07:59.999999，to_char格式化时会直接截断秒的小数部分，最终输出为00:07:59。

执行以下语句可以查看字段的完整精度值，确认亚秒部分的差异：

SELECT 
  to_char(date_started, 'yyyy-mm-dd hh24:mi:ss.US') AS full_start,
  to_char(date_completed, 'yyyy-mm-dd hh24:mi:ss.US') AS full_completed
FROM my_table;

如果只需要按整秒计算时间差，可以先对两个时间戳做截断到秒的处理，再计算差值：

-- 方法1：先截断到秒再计算差值
SELECT to_char(AGE(date_trunc('second', date_completed), date_trunc('second', date_started)), 'hh24:mi:ss') AS time_diff
FROM my_table;

-- 方法2：计算秒差后转换为时间格式
SELECT to_char((EXTRACT(EPOCH FROM date_completed) - EXTRACT(EPOCH FROM date_started)) * INTERVAL '1 second', 'hh24:mi:ss') AS time_diff
FROM my_table;

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