英文:
Time difference getting different result
问题
以下是您提供的查询代码的翻译部分：
我正在使用以下查询：
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"
请注意，这是SQL查询的翻译，不包括结果或问题的回答。如果您需要进一步的帮助，请随时提出。
英文:
I'm using the following query :
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"
Output :
----------------------------------------------------------------------------------------------------
| duration | duration 2 |
----------------------------------------------------------------------------------------------------
| 262 days 23 hours 30 minutes 0.000000 seconds | {"months":8,"days":23,"minutes":30,"seconds":19} |
----------------------------------------------------------------------------------------------------
On this website I have the following result (https://www.timeanddate.com/date/durationresult.html?d1=09&m1=06&y1=2022&d2=27&m2=02&y2=2023&h1=15&i1=39&s1=00&h2=15&i2=09&s2=00):
262 days, 23 hours, 30 minutes and 0 seconds
8 months, 17 days, 23 hours, 30 minutes
Demo : https://www.db-fiddle.com/f/7aYekUESoZwv6DJv77VaCW/0
Why do I have different result ?
EDIT :
22725019 is the result of
((1677506971778 - 1654781952676) / 1000)
答案1
得分: 2
你可以在差值的结果上使用 justify_interval()：
justify_interval('2023-02-27 15:09:00'::timestamp - '2022-06-09 15:39:00'::timestamp)
英文:
You can use justify_interval() on the result of the difference:
justify_interval('2023-02-27 15:09:00'::timestamp - '2022-06-09 15:39:00'::timestamp)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论