2

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

  • 3
    You can use justify_interval() to "normalize" the value: postgresql.org/docs/current/static/… – a_horse_with_no_name Oct 11 '16 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 '16 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. – a_horse_with_no_name Oct 11 '16 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 '16 at 20:29
  • The question is: why does it matter? – a_horse_with_no_name Oct 11 '16 at 20:31
2

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
  • 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 '16 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.