specify CHECK clause to date text field

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

specify CHECK clause to date text field

Martin-2
Hi,

Using the precompiled CLI for MacOS (10.13.6) I stumbled upon the following to do
with the date() function:

sqlite> .version
SQLite 3.29.0 2019-07-10 17:32:03 fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6
zlib version 1.2.11
clang-10.0.1
sqlite> select date('2019-02-00');  -- null

sqlite> select date('2019-02-01');  -- ok
2019-02-01
sqlite> select date('2019-02-1' );  -- null

sqlite> select date('2019-02-29');  -- not a leap year
2019-02-29
sqlite> select date('2019-02-31');  -- ?
2019-02-31
sqlite> select date('2019-02-32');  -- null

sqlite> .quit

It seems the date function does not check that the date is valid, only the format.
Regardless of month it accepts day numbers from '01' to '31'.

Consequently, I would appreciate any advice on the preferred way to specify a
    CREATE TABLE .. CHECK clause
to guard inserting a yyyy-mm-dd date into a text field.

Thanks, Martin.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: specify CHECK clause to date text field

Clemens Ladisch
Martin wrote:
> sqlite> select date('2019-02-29');  -- not a leap year
> 2019-02-29

> I would appreciate any advice on the preferred way to specify a
>     CREATE TABLE .. CHECK clause
> to guard inserting a yyyy-mm-dd date into a text field.

sqlite> select date('2019-02-29', '+0 days');
2019-03-01

CREATE TABLE t (
  date date  CHECK (date = date(date, '+0 days'))
);

(This also ensures that the date is not in Julian day format.)


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: specify CHECK clause to date text field

Adrian Ho
In reply to this post by Martin-2
On 12/8/19 10:59 PM, Martin wrote:

> sqlite> .version
> SQLite 3.29.0 2019-07-10 17:32:03 fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6
> zlib version 1.2.11
> clang-10.0.1
> sqlite> select date('2019-02-00');  -- null
>
> sqlite> select date('2019-02-01');  -- ok
> 2019-02-01
> sqlite> select date('2019-02-1' );  -- null
>
> sqlite> select date('2019-02-29');  -- not a leap year
> 2019-02-29
> sqlite> select date('2019-02-31');  -- ?
> 2019-02-31
> sqlite> select date('2019-02-32');  -- null
>
> sqlite> .quit
>
> It seems the date function does not check that the date is valid, only the format.
> Regardless of month it accepts day numbers from '01' to '31'.
Here's a Dirty Little Secret: All the SQLite date functions are centered
around strftime(), which is not implemented in a strictly correct sense
in *every* Unix-like platform I've seen. SQLite at least does a simple
range check on the day-of-month; using your platform's strftime() C
function, the equivalent of 2019-02-1234 is perfectly legal, and gives
you the date that's 1233 days after Feb 1 2019.
> Consequently, I would appreciate any advice on the preferred way to specify a
>     CREATE TABLE .. CHECK clause
> to guard inserting a yyyy-mm-dd date into a text field.

Off the top of my head, simply "round-trip" the date string to Julian
days and back again, then compare the two dates. Of course, you should
first check that the Julian conversion succeeded.

=====

$ cat test.sql

CREATE TABLE t(a TEXT CONSTRAINT valid_date CHECK (

  strftime('%J', a) IS NOT NULL AND a = date(strftime('%J', a)))

);

INSERT INTO t VALUES('2019-02-00');
INSERT INTO t VALUES('2019-02-01');
INSERT INTO t VALUES('2019-02-1');
INSERT INTO t VALUES('2019-02-28');
INSERT INTO t VALUES('2019-02-29');
INSERT INTO t VALUES('2019-02-30');
INSERT INTO t VALUES('2019-02-31');
INSERT INTO t VALUES('2019-02-32');
SELECT * FROM t;


$ sqlite3 < test.sql

Error: near line 2: CHECK constraint failed: valid_date
Error: near line 4: CHECK constraint failed: valid_date
Error: near line 6: CHECK constraint failed: valid_date
Error: near line 7: CHECK constraint failed: valid_date
Error: near line 8: CHECK constraint failed: valid_date
Error: near line 9: CHECK constraint failed: valid_date
2019-02-01
2019-02-28

=====

--
Best Regards,
Adrian


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: specify CHECK clause to date text field

Adrian Ho
In reply to this post by Clemens Ladisch
On 14/8/19 8:33 PM, Clemens Ladisch wrote:
> CREATE TABLE t (
>   date date  CHECK (date = date(date, '+0 days'))
> );

Sadly, this isn't sufficient for guarding against malformed dates like
'2019-02-00' and '2019-02-1' that the OP listed, because the CHECK
expression in those cases resolves to NULL, which does *not* signal a
constraint violation.

Instead, you have to "round-trip" the date conversion manually, and
check that the intermediate Julian conversion IS NOT NULL, for this
CHECK to be truly effective.

--
Best Regards,
Adrian

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: specify CHECK clause to date text field

nomad
In reply to this post by Martin-2
> It seems the date function does not check that the date is valid,
> only the format.

I've run into the same issue. Don't remember if it has been raised on
the list, but I have a vague memory that it fell into the WONTFIX
category :-(

> Consequently, I would appreciate any advice on the preferred way to
> specify a CREATE TABLE .. CHECK clause to guard inserting a
> yyyy-mm-dd date into a text field.

The following might be instructive:

    .version
    .width 10 10 2 15 2 21 2
    WITH
        x(dt)
    AS (
        VALUES
            (NULL),
            ('2019-02-00'),
            ('2019-02-01'),
            ('2019-02-1'),
            ('2019-02-29'),
            ('2019-02-30'),
            ('2019-02-31'),
            ('2019-02-32')
    )
    SELECT
        x.dt AS "str",
        date(x.dt) AS "date(str)",
        x.dt IS date(x.dt) AS "IS",
        date(x.dt,'utc') AS "date(str,'utc')",
        x.dt IS date(x.dt,'utc') AS "IS",
        date(x.dt,'localtime') AS "date(str,'localtime')",
        x.dt IS date(x.dt,'localtime') AS "IS"
    FROM
        x
    ;

    -- Output

    SQLite 3.22.0 2017-11-27 17:56:14
        465350e55ddaf30cfba7874653301de7238a9dc2bc5e1f800fc95de9360679f6
    str         date(str)   IS  date(str,'utc')  IS  date(str,'localtime')  IS
    ----------  ----------  --  ---------------  --  ---------------------  --
    NULL        NULL        1   NULL             1   NULL                   1
    2019-02-00  NULL        0   NULL             0   NULL                   0
    2019-02-01  2019-02-01  1   2019-01-31       0   2019-02-01             1
    2019-02-1   NULL        0   NULL             0   NULL                   0
    2019-02-29  2019-02-29  1   2019-02-28       0   2019-03-01             0
    2019-02-30  2019-02-30  1   2019-03-01       0   2019-03-02             0
    2019-02-31  2019-02-31  1   2019-03-02       0   2019-03-03             0
    2019-02-32  NULL        0   NULL             0   NULL                   0

I think the above means you can compare the original value with the
localtime equivalent:

    CREATE TABLE table_a(
        dt TEXT -- NOT NULL if you like
        CONSTRAINT valid_date CHECK(dt IS date(dt,'localtime'))
    );

Regards,
Mark
--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: specify CHECK clause to date text field

Richard Hipp-3
In reply to this post by Adrian Ho
On 8/14/19, Adrian Ho <[hidden email]> wrote:
> Here's a Dirty Little Secret: All the SQLite date functions are centered
> around strftime(), which is not implemented in a strictly correct sense
> in *every* Unix-like platform I've seen.

Not true.

SQLite implements its own date and time computations, based on
algorithms taken from Astronomical Algorithms, 2nd Edition, 1998,
ISBN 0-943396-61-1. See https://sqlite.org/src/file/src/date.c for the
latest source code.

An obscure exception is if you compile with
-DSQLITE_OMIT_DATETIME_FUNCS.  In that case SQLite does invoke the
system strftime() routine as a fallback implementation for the
keywords CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP.  But this
only happens when you use the obscure -DSQLITE_OMIT_DATETIME_FUNCS
compile-time option. I am aware of nobody who actually does that.

You can verify the above claim by running "nm" (on many unix systems,
or the equivalent on Mac or Windows) on a compiled sqlite3.o file, and
seeing that "strftime" appears nowhere in the output, while library
routines that SQLite does use like "open", "read", and "malloc" do
appear in the output.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: specify CHECK clause to date text field

Adrian Ho
On 14/8/19 8:47 PM, Richard Hipp wrote:

> On 8/14/19, Adrian Ho <[hidden email]> wrote:
>> Here's a Dirty Little Secret: All the SQLite date functions are centered
>> around strftime(), which is not implemented in a strictly correct sense
>> in *every* Unix-like platform I've seen.
> Not true.
>
> SQLite implements its own date and time computations, based on
> algorithms taken from Astronomical Algorithms, 2nd Edition, 1998,
> ISBN 0-943396-61-1. See https://sqlite.org/src/file/src/date.c for the
> latest source code.
>
> An obscure exception is if you compile with
> -DSQLITE_OMIT_DATETIME_FUNCS.  In that case SQLite does invoke the
> system strftime() routine as a fallback implementation for the
> keywords CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP.  But this
> only happens when you use the obscure -DSQLITE_OMIT_DATETIME_FUNCS
> compile-time option. I am aware of nobody who actually does that.
Apologies, I did indeed missing the #ifdef SQLITE_OMIT_DATETIME_FUNCS
around the currentTimeFunc() definition.

--
Best Regards,
Adrian

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: specify CHECK clause to date text field

Adrian Ho
In reply to this post by nomad
On 14/8/19 8:47 PM, [hidden email] wrote:
>
>     CREATE TABLE table_a(
>         dt TEXT -- NOT NULL if you like
>         CONSTRAINT valid_date CHECK(dt IS date(dt,'localtime'))
>     );

Sorry, that 'localtime' qualifier is a non-starter; that will throw a
"non-deterministic function in index expression or CHECK constraint"
error
(https://www.sqlite.org/deterministic.html#special_case_processing_for_date_time_functions):

    The built-in date and time functions of SQLite are a special case.
    These functions are usually considered deterministic. However, if
    these functions use the string "now" as the date, or if they use the
    localtime modifier or the utc modifier, then they are considered
    non-deterministic. Because the function inputs are not necessarily
    known until run-time, the date/time functions will throw an
    exception if they encounter any of the non-deterministic features in
    a context where only deterministic functions are allows.


--
Best Regards,
Adrian

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: specify CHECK clause to date text field

nomad
On Wed Aug 14, 2019 at 09:08:26PM +0800, Adrian Ho wrote:

> On 14/8/19 8:47 PM, [hidden email] wrote:
> >
> >     CREATE TABLE table_a(
> >         dt TEXT -- NOT NULL if you like
> >         CONSTRAINT valid_date CHECK(dt IS date(dt,'localtime'))
> >     );
>
> Sorry, that 'localtime' qualifier is a non-starter; that will throw a
> "non-deterministic function in index expression or CHECK constraint"
> error
> (https://www.sqlite.org/deterministic.html#special_case_processing_for_date_time_functions):

Right you are. I should have checked that properly. Looks like '+0
days' is the right way to go then, still using the IS operator to
handle NULLs.

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: specify CHECK clause to date text field

Keith Medcalf
In reply to this post by nomad

Converting the date from naive (unknown timezone) format to naive utc or naive localtime will result in a different date and it will not match for valid dates depending on the timezone of your computer and the vagaries of the OS localtime conversions, and the particular time-of-day at which the computation is run, which will internally vary depending on whether Standard, Daylight, Double Daylight, or other times happen to be in effect at that locale.   Attempting to add 0 days will simply get back what you put it, round tripped through the conversion to and from julian (with the current timezone base).

CHECK (dt IS date(dt, '+0 days')) is where I would go.

create table dt
(
  dt text check (dt IS date(dt, '+0 days'))
);
insert into dt values (null);
insert into dt values ('2019-02-1');
insert into dt values ('2019-02-28');
insert into dt values ('2019-02-29');
insert into dt values ('2019-02-30');
insert into dt values ('2019-02-31');
insert into dt values ('2019-03-01');

Mutatis mutandis for datetime and time.  Note however that things such as the localization (timezone offset) and fractional seconds will not be permitted or preserved in those cases.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of [hidden email]
>Sent: Wednesday, 14 August, 2019 06:47
>To: SQLite mailing list
>Subject: Re: [sqlite] specify CHECK clause to date text field
>
>> It seems the date function does not check that the date is valid,
>> only the format.
>
>I've run into the same issue. Don't remember if it has been raised on
>the list, but I have a vague memory that it fell into the WONTFIX
>category :-(
>
>> Consequently, I would appreciate any advice on the preferred way to
>> specify a CREATE TABLE .. CHECK clause to guard inserting a
>> yyyy-mm-dd date into a text field.
>
>The following might be instructive:
>
>    .version
>    .width 10 10 2 15 2 21 2
>    WITH
>        x(dt)
>    AS (
>        VALUES
>            (NULL),
>            ('2019-02-00'),
>            ('2019-02-01'),
>            ('2019-02-1'),
>            ('2019-02-29'),
>            ('2019-02-30'),
>            ('2019-02-31'),
>            ('2019-02-32')
>    )
>    SELECT
>        x.dt AS "str",
>        date(x.dt) AS "date(str)",
>        x.dt IS date(x.dt) AS "IS",
>        date(x.dt,'utc') AS "date(str,'utc')",
>        x.dt IS date(x.dt,'utc') AS "IS",
>        date(x.dt,'localtime') AS "date(str,'localtime')",
>        x.dt IS date(x.dt,'localtime') AS "IS"
>    FROM
>        x
>    ;
>
>    -- Output
>
>    SQLite 3.22.0 2017-11-27 17:56:14
>
>465350e55ddaf30cfba7874653301de7238a9dc2bc5e1f800fc95de9360679f6
>    str         date(str)   IS  date(str,'utc')  IS
>date(str,'localtime')  IS
>    ----------  ----------  --  ---------------  --  ----------------
>-----  --
>    NULL        NULL        1   NULL             1   NULL
>1
>    2019-02-00  NULL        0   NULL             0   NULL
>0
>    2019-02-01  2019-02-01  1   2019-01-31       0   2019-02-01
>1
>    2019-02-1   NULL        0   NULL             0   NULL
>0
>    2019-02-29  2019-02-29  1   2019-02-28       0   2019-03-01
>0
>    2019-02-30  2019-02-30  1   2019-03-01       0   2019-03-02
>0
>    2019-02-31  2019-02-31  1   2019-03-02       0   2019-03-03
>0
>    2019-02-32  NULL        0   NULL             0   NULL
>0
>
>I think the above means you can compare the original value with the
>localtime equivalent:
>
>    CREATE TABLE table_a(
>        dt TEXT -- NOT NULL if you like
>        CONSTRAINT valid_date CHECK(dt IS date(dt,'localtime'))
>    );
>
>Regards,
>Mark
>--
>Mark Lawrence
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: specify CHECK clause to date text field

Adrian Ho
In reply to this post by Adrian Ho
On 14/8/19 8:46 PM, Adrian Ho wrote:

> On 14/8/19 8:33 PM, Clemens Ladisch wrote:
>> CREATE TABLE t (
>>   date date  CHECK (date = date(date, '+0 days'))
>> );
> Sadly, this isn't sufficient for guarding against malformed dates like
> '2019-02-00' and '2019-02-1' that the OP listed, because the CHECK
> expression in those cases resolves to NULL, which does *not* signal a
> constraint violation.
>
> Instead, you have to "round-trip" the date conversion manually, and
> check that the intermediate Julian conversion IS NOT NULL, for this
> CHECK to be truly effective.
Or use the IS operator instead, which slipped my mind. 8-)

--
Best Regards,
Adrian

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users