How are months intervals internally calculated in Postgres?

Question

In PostgreSQL, the interval of '1 month' sometimes counts as 30 days and sometimes counts as 31 days. What are the criteria used to determine this?

I ran the below query to demonstrate my confusion.

select 
  now() - interval '1 month'
, now() - interval '30 days'
, interval '30 days' = interval '1 month'
, interval '31 days' = interval '1 month'

The query returns:

2022-03-27 21:09:30.933434+00 | 2022-03-28 21:09:30.933434+00 | true | false

I would expect the query to return both days on March 28th, since an interval of one month is equal to an interval of 30 days.

Answer 1

It comes down to the specific vs the general where day is the specific and month is not. The same happens with day and hour as in:

select '2022-03-13 12:00 PDT'::timestamptz - '1 day'::interval;
        ?column?        
------------------------
 2022-03-12 12:00:00-08

select '2022-03-13 12:00 PDT'::timestamptz - '24 hours'::interval;
        ?column?        
------------------------
 2022-03-12 11:00:00-08

DST occurred morning of 2022-03-13 in PST/PDT. So a day is generalized to the same time a day ago whereas 24 hours ago is actually 24 hours passing.

In your case:

select 
  now() - interval '1 month'
, now() - interval '30 days';
           ?column?            |           ?column?            
-------------------------------+-------------------------------
 2022-03-27 14:44:33.515669-07 | 2022-03-28 14:44:33.515669-07

The 1 month is going to go back to the same date and time one month back, whereas 30 days is going back an actual 30 days.

In this case:

select '2022-03-30 21:17:05'::timestamp - interval '1 month' ;
  ?column?       
---------------------
 2022-02-28 21:17:05

There is no day 30 in February so it goes to the actual end of the month the 28th.