Strange format of interval value returned by Postg

2019-06-26 20:39发布

问题:

I have PostgreSQL 9.5.0 and I observe strange interval values returned by very similar queries. I thought that then interval is bigger than 24 hours then it is returned with days part like this:

SELECT (1000 * ('2016-02-04 12:00:11'::timestamp - '2016-02-03 12:00:00'::timestamp)::interval)::interval

1000 days 03:03:20

But sometimes hours are not converted to days:

SELECT (1000 * ('2016-02-04 11:00:11'::timestamp - '2016-02-03 12:00:00'::timestamp)::interval)::interval

23003:03:20

How can I force the same output format with days part?

回答1:

Use justify_hours():

SELECT justify_hours(1000 * ('2016-02-04 11:00:11'::timestamp - '2016-02-03 12:00:00'))

   justify_hours   
-------------------
 958 days 11:03:20
(1 row) 

or justify_interval():

SELECT justify_interval(1000 * ('2016-02-04 11:00:11'::timestamp - '2016-02-03 12:00:00'))

        justify_interval         
---------------------------------
 2 years 7 mons 28 days 11:03:20
(1 row) 

See also: Why postgres show two different format for same interval value?