strftime accepts wrong date
(1) By FrankL (frankl) on 2022-07-29 15:21:52 [source]
Hi, please look at this:
sqlite> select strftime('%Y-%m-%d', '2020-09-31');
2020-09-31
sqlite> select strftime('%Y-%m-%d', '2020-09-31', '0 seconds');
2020-10-01
sqlite> select strftime('%Y-%m-%d', '2020-09-32');
= null
Shouldn't return strftime('%Y-%m-%d', '2020-09-31') null, too - as it does strftime('%Y-%m-%d', '2020-09-32')?
If the behaviour with the 31. is intentional one can use the check constraint
check (strftime('%Y-%m-%d', my_date, '0 seconds') = my_date)
to ensure that my_date (given as yyyy-mm-dd) is a valid date.
Frank
(2) By Keith Medcalf (kmedcalf) on 2022-07-29 16:05:36 in reply to 1 [link] [source]
You are all correct.
A valid "year" is four digits, zero-padded on the left, with a value between 0000 and 9999 inclusive.
A valid "month" is two digits, zero-padded on the left, with a value between 01 and 12 inclusive.
A valid "day" is two digits, zero-padded on the left, with a value between 01 and 31.
Some months in some years may have less than 31 days, in which case the days that do not exist in the currect month exist in the next month. This is so because "time" is a procession of days one after each and the "descriptive" given to a specific "day" is entirely arbitrary.
For the purposes of conversion between the "descriptive display format" and the "days count", the subdivisions of the display format are irrelevant.
And yes, check (function(x, '+0 seconds') == x) where function is date/time/datetime or an appropriate strftime with format, will work to validate input validity provided that the contents of x are naive (contains no timezone specifier).
(3) By FrankL (frankl) on 2022-07-29 18:27:14 in reply to 2 [link] [source]
Thank you very much for the detailed explanation. If it's not in the documentation yet (I didn't find it there), it would be worth to include it there (in my opinion).
(4) By Harald Hanche-Olsen (hanche) on 2022-07-30 10:22:47 in reply to 3 [link] [source]
I disagree. I would argue that the current behaviour is an aberration which ideally should be fixed. Given the constraints that Keith listed, I think the code ought to go the extra mile – not really a mile, barely a furlong – and disallow incorrect dates like 2022-09-31, 2022-02-29, or 2024-02-30.
However, this is really a minor flaw, so I am not going to lobby for a change.
But if the current behaviour is documented, the developers tie themselves to the mast, stopping them from fixing this (rather inconcequential) flaw in the future.
The user who feeds nonsensical data into sqlite deserve what they get: Undefined behaviour. That is as it should be, and documenting every instance of undefined behaviour is just going to make the documentation bigger and harder to read. I might add that any suggestion to “improve” the documentation by expanding it tends to meet with considerable resistance from the developers.
(5) By Holger J (holgerj) on 2022-07-30 12:10:37 in reply to 1 [link] [source]
Im not sure, but isn't the strftime() function part of the C library, while SQLite just forwards the call?
(6) By Harald Hanche-Olsen (hanche) on 2022-07-30 15:26:33 in reply to 5 [link] [source]
No. sqlite implements its own date and time conversion functions, which is why format specifiers such as %V for ISO style week numbers are not supported by vanilla sqlite.
In any case, the C library strftime() only converts seconds since the unix epoch to strings, and only in a limited range. The topic of this thread is mostly about parsing time strings, which is more properly the work of strptime() in the standard C library. But that is not used by sqlite, either.