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:

Langill

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.

Rees

Hello, I'm here from the future, and your function is exactly what I needed! However I'm not sure about this part: "(with some minor changes)" what exactly is wrong with your function? – Matchbook

Hi, I didn't mean that the function should be improved. I just changed the function's result type from text to interval (comparing this function to that of the quoted answer). – Rees

Answer 2

From https://www.postgresql.org/docs/9.5/static/functions-formatting.html

to_char(interval) formats HH and HH12 as shown on a 12-hour clock, i.e. zero hours and 36 hours output as 12, while HH24 outputs the full hour value, which can exceed 23 for intervals.

From https://www.postgresql.org/docs/8.2/static/functions-formatting.html

to_char(interval) formats HH and HH12 as hours in a single day, while HH24 can output hours exceeding a single day, e.g. >24.

I'd guess that the first one use implicit format based on the result of the subtraction, and the second use an explicit format based on the input.

Elmoelmore

But if you check pgAdmin output, result is an interval not a string. And HH12 mean 12am/pm so range from 00-12 but HH24 mean 00-23 – Langill

Answer 3

Frankly, the answer klin provided is not correct as casting to int rounds the number. If it rounds up, you get +1 extra hour to the returning value. The correct way is to truncate it. Here is my version:

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

P.S. I can't leave comments under answers as I'm a new user and don't have enough reputation yet.

Thebault