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.

Because it depends on the starting date; if you are in February, it could be 28 or 29 days too. Now, depending on your requirements you may want to add/subtract "30 days", or you want to add/subtract a "month". — The Impaler, Apr 27 '22 at 21:19
Thank you for your comment. In a test I see that 2022-03-30 21:17:05 - interval '1 month' is 2022-02-28 21:17:05. So in this case it just picks the last day of February at the same time since it does not have a 30th day in 2022? — Hino Tama, Apr 27 '22 at 21:28
I’m voting to close this question because it is not about "code" but instead asks why a perfectly reasonable implementation choice of a particular piece of software is unexpected to the asker. Such questions should instead be directed to the vendor of the software. — Bohemian, Apr 27 '22 at 22:08
By this criteria you need to close this question [Increment sequence](https://stackoverflow.com/questions/71390410/how-to-not-increment-sequence-when-conflict-of-insert-occurs) For that matter a great many others as that is a common question in coding, why is something done the way it is? You have closed this question for your own capricious reasons and have abused your position as moderator. Reopen the question. — Adrian Klaver, Apr 30 '22 at 16:07
@Bohemian how can this question be off topic? https://stackoverflow.com/help/on-topic. does the question not have a bit of "source code"? is this not about "a software algorithm"? is postgres not a "software tool commonly used by programmers"? is this not a "specific programming problem"? is this not a "practical, answerable problem"? I don't think this question can be more on topic. — Alex, May 01 '22 at 01:58
@alex you are asking why Postgres date arithmetic works in a particular way. We cannot speak for why Postgres developers/architects made the choices they made. We can only express our guesses at why, which cannot be "correct" or "incorrect" as they are opinions. I've changed the close reason to "opinion based" to reflect this. — Bohemian, May 01 '22 at 03:23
@Bohemian Why is this question opinion-based? "What are the criteria used to determine whether or not a month interval displays as 30 days or 31 days?" I am not asking about design choice at all. — Hino Tama, May 01 '22 at 19:24
[This question is being discussed on Meta](https://meta.stackoverflow.com/q/417803/16886597) — cocomac, May 02 '22 at 04:13
Why does this need to be specific to PostgreSQL? It's basic stuff. Not all months have the same number of days, you learn that at school. This applies to any calculation in any programming language involving date/time in the gregorian calendar. — user692942, May 03 '22 at 10:51
"since an interval of one month is equal to an interval of 30 days." *According to what reasoning*? "What are the criteria used to determine whether or not a month interval displays as 30 days or 31 days?" How about the number of days *that actually are in that month*? I don't understand how this is a question about programming; it would appear to be answered by *looking at a calendar*. Failing that, what is unclear about [the documentation](https://www.postgresql.org/docs/current/functions-datetime.html)? — Karl Knechtel, May 03 '22 at 13:46
@KarlKnechtel interval of 1 month is defined by postgres as 30 days, hence the question. — Alex, May 03 '22 at 16:05
Defined where and how? The demonstrated behaviour seems to disagree. The simple fact of being able to specify `'1 month'` in certain contexts and get the same result as if using `'30 days'`, does not mean that postgres is "defining" a month to be equal to 30 days. Especially if it (demonstrably) *doesn't* behave that way in *other* contexts. It sure looks to me like Postgres' understanding of "1 month" is the number of days in a particular month that is determined by the context. — Karl Knechtel, May 03 '22 at 16:09
https://github.com/postgres/postgres/blob/REL_14_2/src/include/datatype/timestamp.h#L77 https://github.com/postgres/postgres/blob/REL_14_2/src/test/regress/sql/interval.sql#L297 — Alex, May 03 '22 at 16:40

score 12 · Accepted Answer · answered Apr 27 '22 at 21:52

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.

Adding relevant documentation for posterity: https://www.postgresql.org/docs/current/functions-datetime.html — Hino Tama, May 02 '22 at 13:22

How are months intervals internally calculated in Postgres?

1 Answers1