Why do you think that answer is odd? What answer were you expecting?

(The date Ricardo expects is apparently: 1914-02-28)

The choice of dates in SQLite is deliberate. 1914-02-29 does not exist. SQLite normalizes this by rolling it over into the next month, thus giving 1914-03-01. That is the general way the date logic works in SQLite. It constructs an unnormalized date and then normalizes it by rolling it forward into the next month, if necessary. Example:

• Query: date('2023-01-31','+1 month')
• Unnormalized result: 2023-02-31
• After normalization: 2023-03-03

Apparently PostgreSQL decides instead to truncate the date to the previous month.

I like the way that SQLite does it better. That is the way that date interval calculations in SQLite have worked for over 20 years. I don't think we want to change it at this point.

I thought it (perhaps) interesting that, given a mean tropical year close to 365.2422 days:

select date( julianday('2024-02-29') - (110.0 *  365.2422 ) );
1914-03-01

and, moreover, is about 18% of the way toward 1914-03-02.

You neglected to try the full experiment with your test though, didn't you.

Try:

SELECT '2024-02-29'::date - 1 * INTERVAL '110 year' union all SELECT '2024-03-01'::date - 1 * INTERVAL '110 year' union all SELECT '2024-02-28'::date - 1 * INTERVAL '110 year';

It's not a bijective function.

Whatever "standard" is adhered to, you will get fewer possible unique outputs than inputs when you map a leap year onto a normal year.

You can argue over where the folding should occur, but there is, to my mind, much more consistency and logic with 29th Feb mapping to 1st March in a non-leap year than to the 28th. Equally, I would want the 31st April to map to the 1st May and not the 30th April etc etc.

I think this is purely an assumptions problem.

One date is never equal to another date.

Software should implement international standards like iso8601.

If adding or subtracting years to/from a date the result should always be in the same month. Period.

Try it with decently made programming languages and databases and they will all provide the same results.

I have tried lots of date and time calculations with PostgreSQL, Tcl and Java..They are all fine and thus can be a good reference.

If adding or subtracting years to/from a date the result should always be in the same month. Period.

Obviously this is wrong, and would provide an invalid date. As noted previously. So 'period' is incorrect.

Don't misunderstand. I didn't say the number of the day must remain the same, there is just no justification for changing the month when adding a year.

You may think that the people behind PostgreSQL and the Java and TCL libraries don't know their job.

Our calendar is a bit quirky, that's why some knowledgeable people have set some sensible rules, like it or not.

there is just no justification for changing the month when adding a year

But that leads to an incorrect date. 29 Feb plus a year cannot equal 29 Feb in the next year when the 29 Feb doesn't exist. Your 'no justification' argument falls over immediately. The 'rules' you mention are different to your 'don't change the month' rules.

Yes, that's kind of crazy, but that's the official rule.

In which document are these "rules" published?

you have to stay in the same month

Why? There really doesn't seem to be any sensible justification for your assertion, other than your appeal to authority, which you also haven't justified.

"Clever people have decided" and "everyone knows" are great, but I can't find these diktats anywhere.

I would argue that a date of (say) the 42nd March should be normalised to April 11th, in any sensible system that is going to try and parse it rather than just raising an error.

The 368th day of the (non leap😉) year is the 3rd day of the next year, not the 31st December.

Clearly your interpretation is one legitimate view, but this is a ambiguous transformation, there are multiple possible values.
It's not invertible. There is no bijection. It isn't injective.
And therefore you need to make a choice, and the choice is arbitrary.

Whatever your army of expert datetime geniuses (and I have no doubt there are many who prefer your option), it is entirely reasonable to choose the other interpretation.

Your certainty is not convincing, but it is concerning.

https://en.wikipedia.org/wiki/Celestial_Emporium_of_Benevolent_Knowledge

Some really, really clever people agree with you. :)

"Your certainty is not convincing, but it is concerning." broadly captures the common human fault of hubris that has plagued human history.

Where those 'clever people' who 'decided' seem to go off track may be forgetting the calendar (any calendar) is simply one representation of the universe, not the full specification of the universe. To quote the Bard (not the AI one): "The fault, dear Brutus, is not in our stars, But in ourselves, that we are underlings.".

That said, if using another calendar works for planting your crops (ie. Tzolk'in in Yucatec Mayan and Chol Q'ij in K'iche' Mayan calendar), or another calendar for peeking through one's telescope (sidereal time) then by all means forge ahead.

The thing the sticks out to me from this thread is that doing large date range calculations with a general calendar (say the Gregorian) has some rough edges. Given the difficulty of getting uniform use of even daylight savings time, I suspect it is highly unlikely there will ever be uniform agreement on how to handle the edge cases of large date ranges with the Gregorian (I believe having a seance with Pope Gregory VIII is more likely than getting everyone to agree :).

To be clear, I prefer SQLite handling of this issue is left as is.

The design of date-time functions in SQLite goes back to before version 3.0.0 (circa 2004). Backwards-compatibility constraints dictate that the choice of algorithm for dealing with day-of-month overflow may not change. How many millions of legacy apps would that break otherwise?

However, the next release of SQLite will have a new "modifier" ("floor" unless somebody suggests a better name before the next release) that changes the legacy algorithm to the HolgerJ-preferred approach. So programmers can have it either way. (There is also a new "ceiling" modifier that explicitly chooses the existing algorithm, but since that is default, the new modifier is kind of a no-op.)

The latest trunk SQLite that incorporates the "floor" enhancement is available on fiddle for testing.

Sounds like an excellent approach to me. The modifier should allow folks to use whichever calculation is suitable.

There may be more intuitive labels than "floor/ceiling", but beats me what they might be. Some ideas that are NOT directly applicable might be along the lines of "keep dates valid" or "allow non-existent dates" sort of thing (brainstorming ideas, not particularly advocating).

Given the modifiers will be essentially set in stone once released consider giving the naming a bit of time (perhaps holding it for one release cycle?).

All in all, IMO, pretty neat addition.

If adding or subtracting years to/from a date the result should always be in the same month. Period.

With respect, I'm not aware of any justification for that statement.

Certainly adding or subtracting whole year periods from any 29th Feb, such that the new year is not a leap year, is ambiguous.

There is no date (from a non-leap year) that maps onto the 29th Feb. Thus any such functions is not invertible.

Three possibilities would be:

1. Not a date
2. March 1st
3. Feb 28th

You seem keen on option 3., but this is far from universal, and is certainly not what sqlite does, or has ever done.

And therefore, due to the strong backwards compatibility requirement of this project, as far as sqlite is concerned, the only legitimate option is 2.

ISO8601 is silent on this exact issue, as far as I am aware.

Returning 28th Feb 2025 for date('2024-02-29','+1 years') would involve adding 364 days, and is clearly a suggestion determined to create madness.

This problem comes up for more than just leap-days. Here are two typical cases:

• What is one month after 2024-10-31? Is it 2024-11-30 or 2024-12-01?
• What is one year after 2024-02-29? Is it 2025-02-28 or 205-03-01?

ISO8601 is silent on this issue, as far as I can tell. If you do an internet search, you will get different answers on different websites. There appears to be no consensus.

Let's refer to this at the "Month Overflow Problem". There are two main algorithm, which I will call "Truncate To Same Month" and "Overflow To Next Month". SQLite uses the Overflow To Next Month algorithm. Some other systems do the same. Still others use the Truncate To Same Month algorithm.

SQLite could be enhanced to do both, in a backwards-compatible way. We just need to define new date-span keywords. Perhaps "pg-month" and "pg-year" instead of "month" and "year". ("pg" for Postgresql, since that is how PG does it.) So we would have:

• date('2024-02-29','+1 year') → 2025-03-01
• date('2024-02-29','+1 pg-year') → 2025-02-29

But "pg-year" seems a little goofy. What suggestions do you have for better names for "month" and "year" that use the Truncate-To-Same-Month algorithm?

A prototype for the "pg-month/pg-year" modifier is on a branch: https://sqlite.org/src/timeline?r=month-truncate.

The prototype works well enough with the number of months or years added is an integer. But it makes a lot less sense when the number has a fractional component. Consider:

• `datetime('2024-10-31 22:00', '+1.0333335 months');

The extra 0.0333335 months is equivalent to one day. So what answer do we expect here? Or, what answers would you expect for the following (using either algorithm)?

• `date('2024-10-16','+0.5 months');
• `date('2024-11-16','+0.5 months');

So when you start thinking in terms of fractional months and years, the answer is not so clear-cut is it? To my mind, the Overflow-To-Next-Month algorithm seems to make a whole lot more sense when dealing with fractional months and years.

If you want to allow fractional months and years, you have to give up on months and years having integral numbers of days.

The Gregorian calendar defines a cycle of 400 years, 97 of which are leap years. Thus, an average year is 365+97/400 days, or 31556952 seconds. An average month then becomes 1/12 of that, or 2629746 seconds.

Using these definitions, datetime('2024-02-29 12:00:00','+1 year') would return '2025-02-28 17:49:12'.

The question here is "What is meant by +1 month? Or +1 year?" That is, what is a month and what is a year in this context. Perhaps as a great expert on ISO-8601, you could quote the definitions of these terms for us.

What is the date that exactly one year after 2024-02-29? Is it 2025-02-28 or is it 2025-03-01?

• Google search says 2025-03-01.

• www.timeanddate.com says 2025-02-28.

• www.calculator.net says 2025-02-28

• www.calculateconvert.com says 2025-03-01

• ChatGPT says 2025-02-28

• Bing says 2025-02-28

So the answer appears to depend upon whom you ask.

Indeed, and 'month' and 'year' can even mean different things to the same person, depending on context of their calculations (and Bertrand would still apply).

It might be worth noting that the connection of the ambiguous dates to imaginary numbers has a mathematical foundation (page 71 of https://mulpress.mcmaster.ca/russelljournal/article/download/1733/1759/2049).

The likely resolution will be the addition of two new date/time modifiers - "mnth" and "yr". These work like "month" and "year" except that they use a different algorithm to resolve day-of-month overflow. Instead of rolling the date forward into the next month (as "month" and "year" do), the date will be truncated to the end of the previous month. Thus we have:

• date('2024-02-29','-110 years') → 1914-03-01
• date('2024-02-29','-110 yr') → 1914-02-28

You can try this change out on fiddle.

The mnemonic is that "mnth" and "yr" are "month" and "year" with vowels omitted, because they work the same as "month" and "year" except that they might omit days in order to keep the month-number the same.

This enhancement is currently on trunk and will appear in the next release unless somebody talks me out of it between now and then.

Edit: Superseded by forum post 0c4e183b2ff.

For what it's worth, I think there is far more logic in the current way sqlite approaches this.

As that's not being lost, I'm happy.

Dates and times drive people utterly mad, so as long as this is well documented it may help. I predict various roars of distress in coming years (especially in 4 year's time again) about this though.

You can please all of the people some of the time, and some of the people all of the time, but you can't please anyone about date times. I think that's how the saying goes.

I have to say that I think this is unwise. Some people who appear unable to read documentation for comprehension, will think that mnth and yr are just abbreviations for month and year. These people will then be caught out when a pedant like me comes along and chnges their mnth/yr to month/year "for readability and maintainability".

I think you're making a rod for your own back. As long as the edge cases are clearly listed along with their outcomes in the existing documentation, that is.

My 2p.

See?

I nod in agreement with @Clothears. This seems like a kluge that I would cook up in my programming and then, two months later, wonder what the heck did I do that for. Seriously, we are not constrained by DOS's artificial 8.3 name restrictions anymore. Why not just create a syntax that actually reads well and makes immediate sense instead of having to look up the documentation for clarification?

My contribution is to keep the existing syntax, and a new keyword "ROUNDED" to show that the result is within the starting month. IMO, this extra keyword is less-subtle, and easier to explain. Comments?

TRUNCATED

ceiling(date('2024-02-29','-110 years')) → 1914-03-01
floor(date('2024-02-29','-110 years')) → 1914-02-28

alternatively

date('2024-02-29','-110 years', 'ceiling') → 1914-03-01
date('2024-02-29','-110 years', 'floor') → 1914-02-28

or

date('2024-02-29','-110 years', 'round up') → 1914-03-01
date('2024-02-29','-110 years', 'round down') → 1914-02-28

with the following as default behavior

date('2024-02-29','-110 years') → 1914-03-01

I like the "ceiling" and "floor" approach. So I have now modified trunk (and fiddle) to use it.

Examples:

What is the date one year after 2024-02-29? The answer can be either 2025-03-01 or 2025-02-28. The first answer comes from "ceiling" and the second comes from "floor".

• date('2024-02-29','+1 year','ceiling') → 2025-03-01
• date('2024-02-29','+1 year','floor') → 2025-02-28

What is 2 years and 6 months after 2018-08-31? The answer is either 2021-02-28 or 2021-03-03:

• date('2018-08-31','+0002-06-00','ceiling') → 2021-03-03
• date('2018-08-31','+0002-06-00','floor') → 2021-02-28

The changes at check-in f0831cced2c919e4 supersedes the changes describe at post 5b791b4a20 earlier in this thread.

Default being ceiling?

Of course. Otherwise there would be a compatibility break.

Splendid.

After having 'fiddled' around with it, I find I have to work through whether or not 'ceiling' is moving the date forward (perhaps since it's a 'limit' function). And similar mental pause goes for 'floor'. Thus, while the functions certainly work, it does not exactly feel right. (Perhaps thinking along the lines of 'earliest possible date', or 'latest possible date' would eliminate my mental pause with ceiling/floor).

One alternate idea is to use something like 'correct/adjust_forward' and 'correct/adjust_back' (because 'rounding' does not exactly fit either). To me, this would eliminate the thought that 'ceiling' caps the month and then going 'oh right, it moves it to the next one'.

This would give a later option to actually handle "allow_invalid/no_adjust" (perhaps a case where an intermediate calculation wants to allow it - presumably to be adjusted again before the final calculation? - without putting the invalid date into the date parser). While it's not clear there ever should be an 'allow_invalid', I can't exactly rule it out either.

While I'm not sure there is anything better than ceiling/floor I can say after playing around with it (and pausing, and coming back to it for a fresh look) ceiling and floor seem less than intuitive. It just feels like there ought to be a better descriptive term.

In any event, I wanted to get back to you with what I thought while using it. Wish I had a better answer :)

Why not

monthshort and yearshort

monthtrunc and yeartrunc

month28 and year364