Interval values for aggregate function AVG greater than 24 hours

Question

I have a simple query which runs the AVG aggregate function on a date time interval field generated by subtracting one datetime field from another.

The query looks like :

SELECT AVG(date_field_1 - date_field_2) 
FROM table 
WHERE date_field_1 > date_field_2 
AND date_field_1 IS NOT NULL 
AND date_field_2 IS NOT NULL 
....

However the query is returning values such as 2 days 24:50:26.092199 in the default postgres interval style . The absolute value I get when I extract EPOCH and convert to seconds (EXTRACT(EPOCH FROM (AVG(date_field_1 - date_field_2))) is something like 262226.092199. I also get results like 26 hours not just 24 hours.

Should the output not be 3 days 00:50:26.092199? .

Answer 1

As @horse_with_no_name already comment, you can use the justify_* funcions, take a look:

WITH config AS (
SELECT '3 days 0 hours 50 minutes 26.092199 seconds'::interval as reference
), tests AS (
    SELECT 
        extract(epoch from reference) as epoch
    FROM config
)
SELECT 
    config.reference, 
    tests.epoch,
    (tests.epoch::text || ' seconds ')::interval,
    justify_days(config.reference) as justify_days,
    justify_hours(config.reference) as justify_hours,
    justify_interval(config.reference) as justify_interval
FROM config, tests;

Take a look at the output:

postgres=# \x
Expanded display is on.
postgres=# WITH config AS (
postgres(# SELECT '3 days 0 hours 50 minutes 26.092199 seconds'::interval as reference
postgres(# ), tests AS (
postgres(#     SELECT
postgres(#         extract(epoch from reference) as epoch
postgres(#     FROM config
postgres(# )
postgres-# SELECT
postgres-#     config.reference,
postgres-#     tests.epoch,
postgres-#     (tests.epoch::text || ' seconds ')::interval,
postgres-#     justify_days(config.reference) as justify_days,
postgres-#     justify_hours(config.reference) as justify_hours,
postgres-#     justify_interval(config.reference) as justify_interval
postgres-# FROM config, tests;
-[ RECORD 1 ]----+-----------------------
reference        | 3 days 00:50:26.092199
epoch            | 262226.092199
interval         | 72:50:26.092199
justify_days     | 3 days 00:50:26.092199
justify_hours    | 3 days 00:50:26.092199
justify_interval | 3 days 00:50:26.092199

Please, take a look at the documentation^[1] for more details.

I hope it helps.

References:

1. https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TABLE