3

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? .

5
  • 3
    You can use justify_interval() to "normalize" the value: postgresql.org/docs/current/static/…
    – user1822
    Oct 11, 2016 at 19:18
  • thank you @a_horse_with_no_name I was not aware of this function. Is the output not supposed to be default justified ? It seems confusing that justification for some part of the value happens automatically but not rest ?
    – Manquer
    Oct 11, 2016 at 20:15
  • I think that has to do with the fact that not all months have the same length and a day is not always 24 hours (the manual also explains that). If you want to get details on that, ask this on the Postgres mailing list.
    – user1822
    Oct 11, 2016 at 20:21
  • I have seen that info in the interval docs, it was my understanding that was only applicable when DST is involved or uneven months.( less than 3 days and no DST involved) Since my data did not have either I assumed that it should not impact my case. Thx I will follow it up on the mailing list with reference dataset.
    – Manquer
    Oct 11, 2016 at 20:29
  • The question is: why does it matter?
    – user1822
    Oct 11, 2016 at 20:31

1 Answer 1

3

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
1
  • thx for the answer. My default value is 2 days 24: 50:29,092199. The justify function does work for me, however I have the same question i asked @a_horse_with_no_name. Should the output not be justified by default ? How is that some part of output got justified but not rest? i.e I did not get 72:50 or 3 00:50 but 2 days 24:50 ? ( i tried using all interval output types with similar effect )
    – Manquer
    Oct 11, 2016 at 20:19

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .