Search code examples
postgresqlpostgresql-13

Age Less than or Equal to a month evaluates to False

When evaluating intervals, postgres appears to define a month as 30 days exactly, even when there are 31 days in a month:

select age('2021-03-31 23:59:59.999', '2021-03-01'::date)

Returns: 30 days 23:59:59.999

Which in the case of March is Less than 1 month.

Yet:

select age('2021-03-31 23:59:59.999', '2021-03-01'::date) <= '1 month'

Evaluates to false.

A (not very clean) solution to this is:

select age('2021-03-31 23:59:59.999', '2021-03-01'::date) <= case (select DATE_PART('days', DATE_TRUNC('month', '2021-03-31'::Date) + '1 MONTH'::interval - '1 DAY'::INTERVAL)) 
when 31 then '31 days'::interval when 30 then '30 days'::interval 
when 29 then '29 days'::interval else '28 days'::interval end

My question is in 2 parts:

  1. Why does postgresql define a month as 30 days, particularly in the case where I give two dates as input to a builtin function?
  2. Is there a cleaner solution to my problem than the above snippet?
Solution

  • Perhaps interval '1 month' is ambiguous. Is it 28, 29, 30 or 31 days as all them are correct depending upon which month. With nothing to compare it seems to just choose 1. Try reformulating the comparison.

    select '2021-03-31 23:59:59.999'::timestamp - interval '1 month' <  '2021-03-01'::date