SQLite Forum

date function is broken

date function is broken

(1) By sm1104 on 2023-11-04 15:51:21 [source]

sqlite> select date('2023-02-29'); 2023-02-29

sqlite> select date('2023-02-30'); 2023-02-30

SQLite version 3.37.2 2022-01-06 13:25:41 Linux 6.2.0-36-generic #37~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Mon Oct 9 15:34:04 UTC 2 x86_64 x86_64 x86_64 GNU/Linux

(2) By SeverKetor on 2023-11-04 16:09:41 in reply to 1 [link] [source]

Garbage in, garbage out I guess?

(3) By Harald Hanche-Olsen (hanche) on 2023-11-04 16:41:50 in reply to 1 [link] [source]

You could try select date('2023-02-30','0 days');. That fources normalization of the date.

(4) By L Carl (lcarlp) on 2023-11-04 18:08:27 in reply to 3 [link] [source]

Yes, normalizing works, but most bad dates return null, and I think this should also return null for consistency, but I would not consider this an egregious problem.

(10) By anonymous on 2023-11-07 02:32:09 in reply to 3 [link] [source]

...That fources normalization of the date

It's hardly a normalization, more like an "autocorrect".

The invalid date should be flagged with a warning at least or just return NULL to be consistent with date('2023-01-32','0 days').

(11) By Gunter Hick (gunter_hick) on 2023-11-07 06:59:52 in reply to 10 [link] [source]

Do I understand you corectly that you are asking to break an unknown number of applications for no better reason than "it ought to be so"?

(12) By Harald Hanche-Olsen (hanche) on 2023-11-07 08:29:20 in reply to 11 [link] [source]

I suppose if it was considered important enough, one could add a pragma to enforce more stringent rules for date parsing. Is it worth the trouble? My guess is no, but the possibility exists.

(13) By anonymous on 2023-11-07 14:34:33 in reply to 11 [link] [source]

...break an unknown number of applications

Flagging with a warning does not equate "breaking". More so, it's unknown if the "autocorrect" behavior was yielding the expected value in the affected applications.

(14) By Gunter Hick (gunter_hick) on 2023-11-07 14:57:45 in reply to 13 [link] [source]

Ok then genius, how do you propose "flagging with a warning" to proceed in a way that does not break legacy applications? Especially considering that you do not know if and how many applications can deal with or even rely upon the current behaviour.

(15) By anonymous on 2023-11-07 15:20:10 in reply to 14 [link] [source]

I'd take it as your genuinely trying to find solution to this issue and not wasting words in pointless arguments.

The warning can be issued with utf8_printf(stderr, "Warning: ...") or similar just as for other cases needing to signal a warning in SQLite.

(16) By Larry Brasfield (larrybr) on 2023-11-07 15:55:19 in reply to 15 [link] [source]

The warning can be issued with utf8_printf(stderr, "Warning: ...") or similar just as for other cases needing to signal a warning in SQLite.

What you seem to misapprehend is that where the error occurs is in the SQLite library. There is no warning possible. You assume that it can just write to stderr. That might be fine for an application, especially a console app. It would be exceedingly presumptuous and likely inappropriate or impossible for the library. In a windowed application, stderr may not be available. And it is not the library's role to use side-channels.

I'd take it as your genuinely trying to find solution to this issue and not wasting words in pointless arguments.

Clearly not pointless. The "genius" may have been too much.

(17) By Warren Young (wyoung) on 2023-11-07 16:11:44 in reply to 16 [link] [source]

The function call in question (utf8_printf()) is only used in shell.c, so one may presume that the anonymous poster up-thread is asking for the shell to diagnose this, not SQLite proper.

How one is meant to do that when it isn't the shell parsing and interpreting the dates is a different question.

Something I haven't seen come up in this thread is, what does the standard say? How do other SQL engines handle this? Is SQLite an outlier here, or is it doing what everyone else does with this GIGO case?

(18) By Roger Binns (rogerbinns) on 2023-11-08 00:12:13 in reply to 16 [link] [source]

There is no warning possible

SQLite does have a log available and does issue warnings that way. For example

select "hello";
has the log handler called with a code of SQLITE_WARNING and message of
double-quoted string literal: "hello"

(19) By Gunter Hick (gunter_hick) on 2023-11-08 06:29:08 in reply to 18 [link] [source]

That I would be OK with because it

- does not produce spurious output on streams that may not be available or used for other purposes
- does not change the return value of the function
- does not waste CPU cycles in the case of "proper" input

in effect, being invisible to both the application and the user unless the application is specifically set up to handle the error log callback.

(5) By anonymous on 2023-11-04 18:11:32 in reply to 1 [link] [source]

try this:

select date(julianday('2023-02-29'))

interesting ha.

(6) By SeverKetor on 2023-11-04 22:38:16 in reply to 1 [link] [source]

As of earlier today there's a commit to ensure that the YYYY-MM-DD input to date and time functions has been normalized prior to returning a result.

sqlite> select date('2023-02-30');

(7) By anonymous on 2023-11-04 22:48:38 in reply to 6 [link] [source]

Hmmm... no sanity check?

What does select date('2022-02-365') return with this sort of normalization?

I'm guessing something like '2024-02-01'

Shouldn't an invalid date be recognized as invalid rather than forced to a valid but possibly incorrect value.

(8) By Stephan Beal (stephan) on 2023-11-05 00:13:09 in reply to 7 [link] [source]

I'm guessing something like '2024-02-01'

It's NULL. Any day number of 32 or higher is treated as outright invalid (NULL). Any day number of 31 or less will, for months with fewer days, wrap around to the next month.

Shouldn't an invalid date be recognized as invalid rather than forced to a valid but possibly incorrect value.

Arguably so, but backwards compatibility constraints restrict the level of changes which can be made to the date/time functions.

(9) By anonymous on 2023-11-06 03:00:37 in reply to 1 [link] [source]

I suggest the logic of duckdb

D select date'2023-02-29';
Error: Conversion Error: date field value out of range: "2023-02-29", expected format is (YYYY-MM-DD)
D select date'2023-02-27';
│ CAST('2023-02-27' AS DATE) │
│            date            │
│ 2023-02-27                 │

(20) By sm1104 on 2023-11-08 20:54:40 in reply to 9 [link] [source]

I think it is pretty simple - database engine must have built-in check to validate date. This is, I think, how it works in any other database. For example, this is mysql (MariaDB) response: select date('2023-02-29'); +--------------------+ | date('2023-02-29') | +--------------------+ | NULL | +--------------------+ 1 row in set, 1 warning (0.000 sec)

MariaDB select date('2024-02-29'); +--------------------+ | date('2024-02-29') | +--------------------+ | 2024-02-29 |

I think, SQLite treats February as ordinary month, thinking it might have maximum 31 days. And, unfortunately, SQLite engine is not aware of existence of leap years.

(21) By Larry Brasfield (larrybr) on 2023-11-08 21:18:05 in reply to 20 [link] [source]

And, unfortunately, SQLite engine is not aware of existence of leap years.

Do you have any evidence to support that assertion? I believe it is false.

(22) By sm1104 on 2023-11-09 06:10:23 in reply to 21 [link] [source]

@larrybr - Did you have a chance to read my post? according to what sqlite3 gave back to me, it treats February as a ordinary month and 'thinks' it has maximum 31 days in it. for 2023-02-29 it happily returns this date, meaning from sqlite3 perspectives - it exists in 2023. It does the same for 2024-02-29, which is correct. but it is correct answer not because sqlite validated it - it is because it is under impression that there are 31 days in February sqlite> select date('2023-02-29'); 2023-02-29 sqlite> select date('2023-02-30'); 2023-02-30 sqlite> select date('2023-02-31'); 2023-02-31 sqlite> select date('2023-02-32');


As you can see the only date it refused to reply was 2023-02-32. Am I missing/do not understand something here?

Actually... after simple check it is even more ridiculous - turned out, sqlite3 date function is under impression that all months in a year might have maximum 31 days... January, February, March, April... so on. sqlite> select date('2023-02-31'); 2023-02-31 sqlite> select date('2023-03-31'); 2023-03-31 sqlite> select date('2023-04-31'); 2023-04-31 sqlite> select date('2023-05-31'); 2023-05-31 sqlite> select date('2023-06-31'); 2023-06-31 sqlite> select date('2023-07-31'); 2023-07-31 sqlite> select date('2023-08-31'); 2023-08-31 sqlite> select date('2023-09-31'); 2023-09-31 sqlite> select date('2023-10-31'); 2023-10-31 sqlite> select date('2023-11-31'); 2023-11-31 sqlite> select date('2023-12-31'); 2023-12-31 sqlite>

(24) By SeverKetor on 2023-11-09 11:01:18 in reply to 22 [link] [source]

Well, you've got three options: update when it's available since this issue has already been addressed, or do date(whatever, '0 days') to force SQLite to normalize your bad input, or stop putting in bad dates.

(25) By Gunter Hick (gunter_hick) on 2023-11-09 11:06:06 in reply to 22 [link] [source]

see https://sqlite.org/forum/forumpost/4881adaae991d922

(26) By Larry Brasfield (larrybr) on 2023-11-09 12:28:02 in reply to 22 [link] [source]

@larrybr - Did you have a chance to read my post?

Yes, that's why I replied, asking for proof of your assertion, "SQLite engine is not aware of existence of leap years."

I wonder how a logical argument to that effect would survive this: sqlite> SELECT timediff('2023-02-28','2023-03-01'); -0000-00-01 00:00:00.000 sqlite> SELECT timediff('2024-02-28','2024-03-01'); -0000-00-02 00:00:00.000 sqlite> , which is a screen scrape showing the contrary.

... As you can see the only date it refused to reply was 2023-02-32. Am I missing/do not understand something here?

Yes. You have confounded the library's treatment of invalid dates with its treatment of valid dates. Your assertion just disproved is what is known as a "weak inference". Such an inference is not necessarily false, but is not logically proven by the facts upon which it is based.

If you had said "SQLite does not apply its knowledge of leap years in every context in which it might be applicable.", then I would not have denied it. That is a logically correct deduction.

(27) By anonymous on 2023-11-09 18:24:21 in reply to 26 [link] [source]

Looking for the truth in the code:

  • date.c:dateFunc() in question does not appear to do any month-specific validation in case of YMD dates;
  • date.c:parseYyyyMmDd() does enforce the format but not the validity of the actual year/month/day combination;
  • the year/month/day set is converted into Julian date;
  • the resulting date is simply a re-formatted YMD from the parsed values.

Meanwhile the timediff() is likely done using the Julian or epoch representation.

Technically, it's possible to the "wrap around" in the date() function too by back-converting from the Julian date. This should return a valid date.

However this would only further cement this "silent-autocorrect" behavior.

(23) By Gunter Hick (gunter_hick) on 2023-11-09 06:52:29 in reply to 20 [link] [source]

No it is not "pretty simple" to change things now, and yes SQLite is aware of leap years. It could not correctly "wrap around to the next month" without actually knowing about them.

Referring to the layer model, the problem is that input validation in the presentation layer is missing or lacking, leading to "slightly invalid" dates being presented to the storage layer. Or calculations in the application layer are either ignorant of the problem, or choose to use SQLite to "fix up" these dates.

Historically, SQLite has chosen to "wrap around" such dates into the next month. We are now stuck with this, independant of "how it should be", because changing the behaviour will break existing applications.

(28) By Bill Wade (billwade) on 2023-11-10 13:06:36 in reply to 23 [link] [source]

It would be helpful (at least for discussions such as these), if the choice was explicitly documented at https://sqlite.org/lang_datefunc.html. If that documentation is there, I'm not finding it.

Some of the behavior might be inferred by "... initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01 ..." but that text seems to be talking about "internal" behavior, rather than treatment of literals passed directly to the the functions.

(29) By anonymous on 2023-11-11 17:41:59 in reply to 23 [link] [source]

...SQLite is aware of leap years

The only parts "aware" of the leap years are the ones that deal with Julian date conversion. That's also the main reason the "wrap around" actually takes place in the SQLite handling of the invalid dates.

Specifically, the invalid YMD is first internally converted into "wrapped around" Julian date. The extra days are simply added to the resulting Julian days. Then any subsequent manipulation of such date will make use of the this pre-computed Julian date.

The manipulation result in Julian date form is then converted back into the YMD, see date.c:computeYMD().

Thus, for the least degree of consistency, such invalid YMD date should be explicitly converted into "wrapped around" YMD form and returned from the date() function. Instead of implicitly keeping a "wrapped around" Julian date, yet returning the invalid YMD date.

Still, a proper warning should be flagged in one way or another. Perhaps some additional condition value could be returned from the API and checked by the caller or some other way to signal the implicit "normalization".

Either way no application would willingly choose to keep explicitly invalid dates in the database. Whether in such invalid date instances the applications would keep and rely upon the "wrapped around" values, it is reasonable to expect that.

Preferably, such active normalization behavior by SQLite engine should be controlled with pragmas or options.