strftime and CHECK

(1) By Bill Wade (billwade) on 2022-10-19 14:48:58 [link] [source]

I notice some of the date/time functions are documented to return null for invalid inputs. Others seem to also do that (date('hello') returns null), but I can't find that explicitly documented.

Is it safe to assume the current behavior is expected? Perhaps I missed some general statement that built-in functions return null when they can't do what they are supposed to do.

If I want to validate (get a runtime error otherwise) that strings are in a specific date/time format (and not null), the correct CHECK seems to be

create table a(x CHECK (ifnull(strftime(myformat, x) = x, 0)));

If I want to allow null, in addition to my specific format, that changes to

create table a(x CHECK (ifnull(strftime(myformat, x) = x, x is null)));

Am I missing some edge case here?

(2) By Keith Medcalf (kmedcalf) on 2022-10-19 15:48:00 in reply to 1 [link] [source]

check(datetime(x, '+0 seconds') IS x) or check(strftime(fmt, x, '+0 seconds') IS x)

This will work whether or not nulls are permitted/contained in the attribute x.

Note that Your Mileage May Vary. Neither datetime nor strftime can "round trip" datetime values that contain a zone specifier -- they must be naive datetimes, and naive datetimes are assumed to be UT1.

(3) By Bill Wade (billwade) on 2022-10-20 13:29:46 in reply to 2 [link] [source]

Thanks for the reply.

I was wondering when the '+0 seconds' modifier would make a difference. If anyone else wonders, it causes sqlite to normalize the result in some cases.

sqlite> select date('2022-06-31');
2022-06-31
sqlite> select date('2022-06-31', '+0 seconds');
2022-07-01

I failed to find a case where the '+' in '+0 seconds' made a difference in the result. It may add some clarity for the reader of the source.

(4) By jose isaias cabrera (jicman) on 2022-10-20 14:11:39 in reply to 3 [link] [source]

sqlite> select date('2022-06-31', '+0 seconds');
2022-07-01

I wonder if this is correct, though. I would expect the return date to be '2022-06-30' because that is the last day of the month.

(5.1) By Gunter Hick (gunter_hick) on 2022-10-20 14:29:30 edited from 5.0 in reply to 4 [link] [source]

It is correct.

There are modifiers for "start of day/month/year" but no corresponding modifiers for "end of ...".

Normalization is only done if the day is 31, everything larger returns NULL.

If you want "end of month" you need to apply 'start of month','+1 month','-1 day' in sequence.

(6.1) By Keith Medcalf (kmedcalf) on 2022-10-20 15:32:25 edited from 6.0 in reply to 3 [link] [source]

The '+' will not make a difference. This "datetime correctification" works by converting the "user said datetime" into a julian day offset, then converting that julian day offset back to "proper proleptic gregorian calendar display format". Anything which causes this to occur without changing the "instant time" will work.

The same thing is achieved by adding and subtracting "the same" days or subparts of days (hours, minutes, seconds, milliseconds, for example).

0 and +0 are the same value (as is -0).

You could also, for example, trigger thusly: datetime(x, '00:00')

(7) By jose isaias cabrera (jicman) on 2022-10-20 16:08:33 in reply to 5.1 [link] [source]

It is correct.

Ok, no problem. That is what was decided when the functions of date were created. And yes, I already use that combination to get the last date of a month using your suggestion. But, if you think of it logically, in my head (which hasn't caught up with technology, yet), I would see someone saying 'give me the last day of this month'. Thanks for the explanation.

I would have love to have date return the date of the last day of the month with a statement like this,

select date('2022-02-31','-0 seconds') AS Date;
┌──────────────┐
│     Date     │
├──────────────┤
│ '2022-02-28' │
└──────────────┘

But, it's just a desire. :-) That would probably also break some legacy applications in backward compatibility.

(8) By Michael A. Cleverly (cleverly) on 2022-10-20 16:38:37 in reply to 7 [link] [source]

You can nest date() calls to get the last day of the month for any arbitrary date (even nonsensical ones like 2022-02-31):

SELECT date(date(date('2022-02-31', 'start of month'), '+1 month'), '-1 day') AS Date;
┌────────────┐
│    Date    │
├────────────┤
│ 2022-02-28 │
└────────────┘

(9) By jose isaias cabrera (jicman) on 2022-10-20 16:59:06 in reply to 8 [link] [source]

Thanks.

(10) By Donald Griggs (dfgriggs) on 2022-10-20 17:49:11 in reply to 8 [source]

And since sqlite guarantees that time modifiers will be applied strictly in left-to-right order, I believe you can even simplify

  SELECT date(date(date('2022-02-31', 'start of month'), '+1 month'), '-1 day') AS Date;

  SELECT date('2022-02-31', 'start of month', '+1 month', '-1 day') AS Date;

(11) By Keith Medcalf (kmedcalf) on 2022-10-20 18:08:20 in reply to 10 [link] [source]

Not to mention that it depends on an implementation detail.

The "correct" answer for the query is: 2022-03-31 -- 2022-02-28 is incorrect.

This is because the "user input date" 2022-02-31 is cannonically 2022-03-03. The "start of month" is then 2022-03-01, +1 month is 2022-04-01 and -1 day is 2022-03-31.