PostgreSQL时间差计算结果不一致原因咨询

问题背景 #

我执行了以下PostgreSQL SQL查询：

SELECT
        concat(
            extract(day from ('2023-02-27 15:09:00'::timestamp - '2022-06-09 15:39:00'::timestamp)),' days ',
            extract(hour from ('2023-02-27 15:09:00'::timestamp - '2022-06-09 15:39:00'::timestamp)),' hours ',
            extract(minute from ('2023-02-27 15:09:00'::timestamp - '2022-06-09 15:39:00'::timestamp)),' minutes ',
            extract(second from ('2023-02-27 15:09:00'::timestamp - '2022-06-09 15:39:00'::timestamp)),' seconds'
        ) as duration,
        justify_interval(make_interval(secs =>22725019)) AS "duration 2"

查询输出：

----------------------------------------------------------------------------------------------------  
| duration                                      | duration 2                                       |    
----------------------------------------------------------------------------------------------------                                  
| 262 days 23 hours 30 minutes 0.000000 seconds | {"months":8,"days":23,"minutes":30,"seconds":19} |
----------------------------------------------------------------------------------------------------  

而日期计算工具给出的结果是：

262 days, 23 hours, 30 minutes and 0 seconds
8 months, 17 days, 23 hours, 30 minutes

补充说明：22725019 是通过 ((1677506971778 - 1654781952676) / 1000) 计算得到的秒数。

核心原因解析 #

1. 秒数对应的起止时间与SQL中的时间不匹配 #

SQL里直接用两个timestamp相减得到的间隔是262 days 23:30:00，换算成秒是：

262*86400 + 23*3600 + 30*60 = 22721400 秒

但你用来计算duration 2的22725019秒，比这个值多了3619秒（1小时0分19秒），这说明你用的两个时间戳1677506971778和1654781952676，对应的起止时间和SQL里写的2023-02-27 15:09:00、2022-06-09 15:39:00根本不是同一组时间——计算基础就错了，结果自然不一致。

2. justify_interval的换算逻辑（正确秒数下与工具一致） #

如果用正确的秒数22721400调用justify_interval：

SELECT justify_interval(make_interval(secs=>22721400));

得到的结果是8 mons 17 days 23:30:00，和在线工具的8 months, 17 days, 23 hours, 30 minutes完全一致。

你之前得到的8个月23天，是因为用了错误的秒数（多了3619秒），导致总时长变成263 days 0:30:19，justify_interval对这个错误时长进行进位换算，才得到了和工具不一样的结果。

额外说明：extract(day)的取值逻辑 #

SQL里的extract(day from interval)取的是间隔中的“天分量”，不是总天数，但在这个场景里，因为间隔是262 days 23:30:00，所以extract(day)就是262，和在线工具的总天数结果一致，这部分是没问题的。

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