0

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.

Improve this question
4
  • 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
    Commented Dec 16, 2019 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
    Commented Dec 16, 2019 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
    Commented Dec 16, 2019 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
    Commented Dec 16, 2019 at 14:21

1 Answer 1

Reset to default
1

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'
Improve this answer
2
  • 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
    Commented Dec 16, 2019 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
    Commented Dec 16, 2019 at 0:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.