1

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?
ThatGuyTy
  • 59
  • 4

1 Answers1

1

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
Belayer
  • 13,578
  • 2
  • 11
  • 22
  • Still confused as to why postgres won't check the inputs to its builtin functions to decide on the length of a variable interval like a month, but this is a nice clean solution to my problem – ThatGuyTy May 06 '21 at 05:46
  • 1
    The built in function **AGE** has completed its task, it calculated the interval between the dates. That is it **only** thing it does. It does not know what you want to do with the result; nor does it care. It returns the interval "30 days 23:59:59.999" Now the less than operator comes into play. It is a boolean operator that knows 1 left operand and 1 right operand. In this case is *30 days 23:59:59.999 < interval '1 month"? I would say that is ambiguous. The computer sees it as 2 totally separate things to process. You are trying to look at it as 1 thing. If you want as 1, write the code. – Belayer May 06 '21 at 06:06