sqlite> select strftime('%Y-%m-%d', 'now');
2022-03-30
sqlite> select strftime('%Y-%m-%d', 'now', '-1 month');
2022-03-02

and

sqlite> select strftime('%Y-%m-%d', '2022-03-30');
2022-03-30
sqlite> select strftime('%Y-%m-%d', '2022-03-30', '-1 month');
2022-03-02

I have to look for the source... I've seen the exact same thing happen in golang. Frankly I'm not sure what was supposed to happen but I would have expected something like FEB.

One month before 2022-03-30 is 2022-02-30, but that date does not exist, so it wraps around to 2022-03-02.

What do you think that answer should be?

I just looked at the code again... and sure enough. It subtracted 1 from the month then normalized the month/year... then normalized the day. Since the day was >28 it had to re-normalize the month.

Frankly I cannot see anything wrong with that. Thanks and sorry for distracting.

Last note: It seems that TCL returns 2022-02-28 instead of 2022-03-02.

That's what I'd do personally but GNU date also behaves as above (2022-03-02).

Yes, and PostgreSQL does the same. So it's very likely that this is the correct behaviour according to ISO8601.

# select date '2022-03-30' - interval '1 month';
┌─────────────────────┐
│ ?column? │
├─────────────────────┤
│ 2022-02-28 00:00:00 │
└─────────────────────┘

I am in possession of the 2004 edition of ISO8601, and it does not specify the interpretation of the month interval to this level of precision. The latest version is from 2019, I believe, but given the ridiculous prices charged for standards, it's anybody's guess what (if anything) it says about it.

I believe that is the worst possible month increment.

When someone states to increase the month, they typically mean increasing/decreasing the MONTH and is very willing to have the days curbed where the month-length cannot accommodate it, but curbing the days is a lot different from simply shoving the non-fitting days into another month.

Most languages* do this sensible thing and actually increases/decreases the MONTH counter in their Month-increment functions, and then picking the same day in the target month, or the last day if said target month does not have enough days. A very simple rule.

I can't imagine any human ever asking for the month to be increased/decreased being happy to see:

  30 March   - 1 Month --> 2 March, or
  31 May     - 1 Month --> 1 May,   or
  31 January + 1 Month --> 3 March, or
  31 May     + 1 Month --> 1 July,
  etc.

(I'm open to arguments in favor of the above result, but I'm betting on it not existing).

I submit the only correct way to "move the date to a next/previous month" is as described above, that would yield:

  30 March   - 1 Month --> 28 Feb (or 29 Feb on a leap-year), and
  31 May     - 1 Month --> 30 April, and
  31 January + 1 Month --> 28 Feb, and
  31 May     + 1 Month --> 30 June,
  etc.

Moreover, if you start on a variable day and then get the next month successively (base-date + n months), let's say for future projections, in the SQLite way, starting on December 31, your dates would go:

Dec 31
Jan 31
Mar 02
Mar 31
May 01
May 31
Jul 01
Jul 31
etc.

Where it should be clear the more accurate increment would be:
Dec 31
Jan 31
Feb 28
Mar 31
Apr 30
May 31
Jun 30
Jul 31
etc.

Again, I'm open to argument for the former.

That said, I'm not arguing for changing this in SQLite - understanding that legacy changes may break someone's code (though it's hard to imagine anyone depending on the above quirk), and Richard's question of "What do you think the answer should be?" might be an honest attempt to gather opinion, but if perhaps it is a doubling down on the current implementation being the "correct" way, or displaying surprise at something else being expected, I would find that surprising.

[*] "Most" in this context obviously means "Most that I am familiar with", I am not informed of ALL and acknowledge there is some probability of being wrong on this point, but if so, I'm still hoping it doesn't invalidate the reasoning.

This kind of date arithmetic defies logic, "month" and "year" not being formally defined.

Both answers 2022-02-28 and 2022-03-02 have their value but none can round-trip (+1 month (- 1 month)) since you get resp. 2022-03-28 and 2022-04-02, so you never get 2022-02-30 back.

One easy way to provide more flexibility would be to create a pair of new modifiers, say calmonth for calendar month and calyear or some other names, yielding 2022-02-28 in this case.

I believe this would still be compatible with 'Lite' (probably little code involved) and leave the choice of behavior to the user.

I submit the only correct way to "move the date to a next/previous month" is as described above, that would yield:

  30 March   - 1 Month --> 28 Feb (or 29 Feb on a leap-year), and
  31 May     - 1 Month --> 30 April, and
  31 January + 1 Month --> 28 Feb, and
  31 May     + 1 Month --> 30 June,
  etc.

If 30 March - 1 Month --> 28 Feb in a non-leap-year, does that mean that 30 March - 1 Month - 1 Day --> 27 Feb? And yet 30 March - 1 Day - 1 Month is presumably 28 Feb?

IMO trying to do any kind of arithmetic with a non-constant unit such as "Month" is a terrible idea; there's no way to implement sensible and consistent semantics. So personally I would advocate for disallowing "± Month," but I do agree with you though, that if it is allowed then interpreting in as "advance/precede month" best satisfies the principle of least surprise.

Most languages* do this sensible thing

I'm not sure that's true.

C: doesn't really provide date arithmetic, but if you start with 2022-01-31 and increment .tm_mon then strftime will happily format an invalid date for you: 2022-01-31T00:00:00 -> 2022-02-31T00:00:00

python: the datetime module doesn't support timedeltas with units greater than day

GNU date: same as sqlite's behaviour: date -d '2022-01-30 + 1 month' +%FT%T -> 2022-03-02T00:00:00

java: clips the day into the target month: java.time.LocalDate.of(2022, 01, 31).plusMonths(1) -> 2022-02-28

ruby: the Date type only allows addition/subtraction of days/seconds. Does provide an explicit "next_month" function that clips the day

golang: time.Date has its own normalisation scheme which is applied after AddDate(0 /* years */, 1 /* months */, 0 /* days */): 2022-01-31T00:00:00 -> 2022-03-03T00:00:00

1.5 out of 6? Anyway the point is, never trust a computer to do what you expect :)

Given how ill defined the notion of an “N months” increment is, I have to ask: What are the use cases where this is important? That someone finds the current behaviour surprising, does not seem like a sufficient reason to change anything. And I would argue that any date logic that depends on a particular interpretation of N month increments is flawed, and should be replaced by logic counting days or weeks.

Speaking of which, I think a new “N weeks” modifier would be more useful, even though you could easily multiply by 7 and use the “N days” modifier instead. It lets you easily specify the Nth Wednesday of any month by applying the “beginning of month” modifier followed by “weekday 3” and “N weeks” modifiers.

But also, having a way to specify the last occurrence of a weekday within a month seems useful. I have encountered several monthly events scheduled for the last Friday (for example) of each month.

Edited to add a rhetorical question: What date is one month prior to February 28? Currently, you'll get January 28. I think there is no strong reason, other than the look-alike dates, to pick that answer over January 31. I mean, one month before the last day of any given month should be the last day of the previous month, right? It stands to reason. Or not, as you may see fit. ;-)

A modifier is a good idea... but a pragma might also be a good idea for the default behavior.

Unfortunately this issue is not limited to +1/-1 month... it's any month where the current DOM is greater than the DIM of the target month. (Moving from Dec 31 to Feb (-3 month) being an example).

Found this article: date calc breaks prod

It encapsulates the issue and gives examples in different langs.

One very interesting side effect... have you ever used VIM where you moved the cursor to the end of the current line and then moved the cursor up/down the document... notice that it always tracks with the last char/original col depending on the length of the line.

pseudo code:

date = '2022-01-31'
for loop from 1 to 6
  newdate = strftime('%Y-%m-%d', date, '1 month')
  print "date -> newdate"
  date = newdate
loop

results:

2022-01-31 -> 2022-03-03
2022-03-02 -> 2022-04-03
2022-04-31 -> 2022-05-03
...

alternately

date = '2022-01-31'
for loop from 1 to 6
  newdate = strftime('%Y-%m-%d', date, '${loop} month')
  print "date -> newdate"
  date = newdate
loop

results:

2022-01-31 -> 2022-03-03
2022-01-31 -> 2022-03-31
2022-01-31 -> 2022-05-01
...

Now let's just ASSUME for a moment that the date math is changed (not corrected) to the other method. Now what will we see?

pseudo code:

date = '2022-01-31'
for loop from 1 to 6
  newdate = strftime('%Y-%m-%d', date, '1 month')
  print "date -> newdate"
  date = newdate
loop

results:

2022-01-31 -> 2022-02-28
2022-02-28 -> 2022-03-28
2022-03-28 -> 2022-04-28
...

alternately

date = '2022-01-31'
for loop from 1 to 6
  newdate = strftime('%Y-%m-%d', date, '${loop} month')
  print "date -> newdate"
  date = newdate
loop

results:

2022-01-31 -> 2022-02-28
2022-01-31 -> 2022-03-31
2022-01-31 -> 2022-04-30
...

if you think you understand dates. You don't! --anon

They all require consideration. Theirs just no way NOT to think about dates and times with great care.

I appreciate that there is a sqlite doc that addresses the question... and that Richard Hipp replied first asking "what did I expect?".

As there are other SQLite docs that talk about SQLite having technical parity with other DBs including postgres I ran the following:

(pg)

select date('2022-03-30') + ('-1 month')::interval;
2022-02-28T00:00:00.000Z

(mysql6.5)

SELECT DATE_ADD("2022-03-30", INTERVAL -1 month);
2022-02-28

(sql server)

SELECT DATEADD(month, -1, '2022/03/30') AS DateAdd
2022-02-28T00:00:00Z

It's perfectly fine that SQLite wants to do this differently. However, for compatibility reasons I would strongly recommend a PRAGMA for global configuration and a strftime() modifier for local... The main justification is there is no simple sql expression that could be constructed to do exactly that and the CLI does not support variables or variable expansion(computing the value outside the sql query).

I suppose anyone could implement an extension.