Does interval '1 month' incorporate month differences and leap years when calculating date/datetime boundaries? If so, how does it work?

I was asked this question, and I'm not sure how to answer it. I am using Postgres as a reference example, I assume it has its own time offset and calculation integration. But how does this take into account timezones, leap years, and other oddities of the calendaring?

I want to know if there's anything in a the SQL spec that defines how the behavior will occur.

  • I can read many meanings into your question. Do you want to know how intervals behave when being added to a timestamp, or do you want to know if the difference between two timestamps can yield an interval in months? – Laurenz Albe Dec 16 '19 at 7:36
  • @LaurenzAlbe not quite. I know the databases handle this in an error-free way, I'm just curious if there's anything in a the SQL spec that defines how the behavior will occur. Haven't found anything. – Jason Dec 16 '19 at 12:50
  • So you are not interested in PostgreSQL behavior, but in what the SQL standard has to say about this, right? – Laurenz Albe Dec 16 '19 at 12:55
  • a quick search on the Postgresql Email archives returns this email back from 2003 a few years before I started using Postgresql postgresql.org/message-id/… postgresql.org/message-id/… – zsheep Dec 16 '19 at 14:21

Yes, if not it would cause all kinds of errors. when doing math on a date for example 1 month ago, it does take the date to be subtract from and figures out what to do from there, it does not just subtract 30 days.

links will answer your question wiki interval another site

also consider this simple query

select '03-01-2019'::date - interval '30 day',
'03-01-2019'::date -interval '1 month'
  • agreed, I know it must be used in some form or fashion based on the lack of obvious errors. But the thing I was missing were your links, and my google-fu was apparently failing. – Jason Dec 16 '19 at 0:02
  • google, i quite using them years ago, went to wiki.postgresql.org first, found the first article. it would take a day or two to write an article to explain intervals and the math. – zsheep Dec 16 '19 at 0:39

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.