Why postgres show two different format for same interval value?

Question

I was helping with this question trying to change the format for the interval.

from '01 day 22:10:37'  to  '46:10:37'

I give a solution with string manipulation. But then I found postgres can show the same interval on two different format.

SELECT '2016-01-27 08:51:02'::timestamp - '2016-01-25 10:40:25'::timestamp end_date,
       '46:10:37'::interval interval_date;

Funny thing. There is a function doing the inverse process

 justify_hours('46:10:37'::interval) --> '1 day 22:10:37'

So I wondering if there is a direct way to solve this problem. And why same interval value has two different result.

pgAdmin output:

Answer 1

When an interval is a difference between two timestamps it is always justified to hours (i.e. it has standard format). Examples:

select
    '2015-01-01 13:0:0'::timestamp - '2014-01-01 23:0:0'::timestamp, --> 364 days 14:00:00
    '2015-01-01 13:0:0'::timestamp - '2014-01-01 03:0:0'::timestamp, --> 365 days 10:00:00
    '2015-01-01 13:0:0'::timestamp - '2015-01-01 03:0:0'::timestamp; --> 10:00:00

Calculations on intervals are executed on date part and time part separately, so they may lead to strange formats. Examples:

select 
    '2 day 1:00:00'::interval- '1 day 2:00:00'::interval,    --> 1 day -01:00:00 (!!)
    '2 day 100:00:00'::interval+ '1 day 60:00:00'::interval, --> 3 days 160:00:00
    '2 day 100:00:00'::interval- '2 day 60:00:00'::interval; --> 40:00:00

For such cases Postgres developers provided the appropriate function for the format standardization:

select 
    justify_hours('1 day -01:00:00'),  --> 23:00:00
    justify_hours('3 days 160:00:00'), --> 9 days 16:00:00
    justify_hours('40:00:00');         --> 1 day 16:00:00

However they did not think that the reverse operation would be needful. In this answer I proposed a function to convert a date part of an interval to hours. I think it can be (with some minor changes) some kind of reverse function for justify_hours():

create or replace function unjustify_hours(interval)
returns interval language sql as $$
    select format('%s:%s',
        (extract (epoch from $1) / 3600)::int,
        to_char($1, 'mi:ss'))::interval;
$$;

select 
    unjustify_hours('23:00:00'),        --> 23:00:00
    unjustify_hours('9 days 16:00:00'), --> 232:00:00
    unjustify_hours('1 day 16:00:00');  --> 40:00:00

The function to_char(interval, text) cannot be helpful here, as

select 
    to_char(interval '23:00:00', 'hh24:mi:ss'),        --> 23:00:00
    to_char(interval '9 days 16:00:00', 'hh24:mi:ss'), --> 16:00:00 (!)
    to_char(interval '1 day 16:00:00',  'hh24:mi:ss'); --> 16:00:00 (!)

Note that an interval can be correctly formatted in many ways:

select 
    justify_hours('100:00:00'),        --> 4 days 04:00:00
    justify_hours('1 days 76:00:00'),  --> 4 days 04:00:00
    justify_hours('2 days 52:00:00'),  --> 4 days 04:00:00
    justify_hours('5 days -20:00:00'); --> 4 days 04:00:00

Per the documentation:

According to the SQL standard all fields of an interval value must have the same sign, so a leading negative sign applies to all fields; for example the negative sign in the interval literal '-1 2:03:04' applies to both the days and hour/minute/second parts. PostgreSQL allows the fields to have different signs, and traditionally treats each field in the textual representation as independently signed, so that the hour/minute/second part is considered positive in this example. If IntervalStyle is set to sql_standard then a leading sign is considered to apply to all fields (but only if no additional signs appear). Otherwise the traditional PostgreSQL interpretation is used. To avoid ambiguity, it's recommended to attach an explicit sign to each field if any field is negative.

and

Internally interval values are stored as months, days, and seconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings time adjustment is involved. The months and days fields are integers while the seconds field can store fractions. Because intervals are usually created from constant strings or timestamp subtraction, this storage method works well in most cases. Functions justify_days and justify_hours are available for adjusting days and hours that overflow their normal ranges.