Checking whether a given date is valid

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

Checking whether a given date is valid

Philip Bennefall
Hi all,

I'm working on a project where I need to verify that a given date
actually existed (like February 29 in a particular year). I am using
SqLite to store a lot of data already and I didn't feel like hunting for
a datetime library online. I figured that there should be a way to use
SqLite's date functions to check this, and came up with the following query:

select 1 where date(julianday('2004-02-29'))=date('2004-02-29');

The above query returns 1 as expected. The following one returns
nothing, as I would also expect:

select 1 where date(julianday('2005-02-29'))=date('2005-02-29');

Now, my question is simply this. Is the query sound? All the tests I
have run thus far have given correct results (invalid days of months,
leap years etc) but are there any pitfalls that I should be aware of? Is
it safe to rely on the conversion between the floating point julianday
and the date string being accurate if the date is in fact valid?

Kind regards,

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

Re: Checking whether a given date is valid

Simon Slavin-3

On 9 Aug 2014, at 9:53pm, Philip Bennefall <[hidden email]> wrote:

> Is it safe to rely on the conversion between the floating point julianday and the date string being accurate if the date is in fact valid?

Probably not.  The safest thing is to turn the string into a number (probably Julian day or epoch) and then turn it back into a date again.  If you get the original string back, it's a legit date.  If you get something else, it might be something like the 30th of February.

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

Re: Checking whether a given date is valid

Keith Medcalf
In reply to this post by Philip Bennefall

The result of converting an "invalid" datetime string with julianday() is a floating point julianday value which corresponds to the normalized datetime.  In your example, date(julianday('2005-02-29')) is '2005-03-01' which is the correct normalized date.

Therefore, a function which will validate that a string is a valid normalized date / datetime / time string is the following:

datetime(julianday(x))=datetime(x)

This returns 1 if the x is a valid normal date, datetime, or time and 0 if not; and, can be used as a check constraint or otherwise.

sqlite> create table mytable
   ...> (
   ...>    data text collate nocase check (datetime(julianday(data)) = datetime(data))
   ...> );
sqlite> insert into mytable values('01:00');
sqlite> insert into mytable values('24:00');
Error: CHECK constraint failed: mytable
sqlite> insert into mytable values('2004-02-29');
sqlite> insert into mytable values('2005-02-29');
Error: CHECK constraint failed: mytable
sqlite> insert into mytable values('0000-01-29');
sqlite> insert into mytable values('-0502-02-29');
Error: CHECK constraint failed: mytable
sqlite> insert into mytable values('-0502-01-29');

Of course, inserting a "numeric" value also passes the test since all strictly numeric values can be converted to valid datetime strings.


>-----Original Message-----
>From: [hidden email] [mailto:sqlite-users-
>[hidden email]] On Behalf Of Philip Bennefall
>Sent: Saturday, 9 August, 2014 14:53
>To: General Discussion of SQLite Database
>Subject: [sqlite] Checking whether a given date is valid
>
>Hi all,
>
>I'm working on a project where I need to verify that a given date
>actually existed (like February 29 in a particular year). I am using
>SqLite to store a lot of data already and I didn't feel like hunting for
>a datetime library online. I figured that there should be a way to use
>SqLite's date functions to check this, and came up with the following
>query:
>
>select 1 where date(julianday('2004-02-29'))=date('2004-02-29');
>
>The above query returns 1 as expected. The following one returns
>nothing, as I would also expect:
>
>select 1 where date(julianday('2005-02-29'))=date('2005-02-29');
>
>Now, my question is simply this. Is the query sound? All the tests I
>have run thus far have given correct results (invalid days of months,
>leap years etc) but are there any pitfalls that I should be aware of? Is
>it safe to rely on the conversion between the floating point julianday
>and the date string being accurate if the date is in fact valid?
>
>Kind regards,
>
>Philip Bennefall
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: Checking whether a given date is valid

Philip Bennefall
In reply to this post by Simon Slavin-3
Hi Simon,

That is what I tried to do in my query examples that I included in the
original message. Based on those, would you say that I am doing it
correctly?

Kind regards,

Philip Bennefall
On 8/9/2014 11:49 PM, Simon Slavin wrote:
> On 9 Aug 2014, at 9:53pm, Philip Bennefall <[hidden email]> wrote:
>
>> Is it safe to rely on the conversion between the floating point julianday and the date string being accurate if the date is in fact valid?
> Probably not.  The safest thing is to turn the string into a number (probably Julian day or epoch) and then turn it back into a date again.  If you get the original string back, it's a legit date.  If you get something else, it might be something like the 30th of February.
>
> Simon.

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

Re: Checking whether a given date is valid

Philip Bennefall
In reply to this post by Keith Medcalf
Hi Keith,

Thanks for the explanation. From what I can see in your example, the
only difference from mine is that I used date(x) and you used
datetime(x). If I am understanding things correctly, this would give the
same behavior if I am only concerned about dates? In this case I don't
care about the time of day; just that the date is valid. So based on
your description I gather that I'm doing it correctly in my example? I hope?

Kind regards,

Philip Bennefall
On 8/10/2014 12:00 AM, Keith Medcalf wrote:

> The result of converting an "invalid" datetime string with julianday() is a floating point julianday value which corresponds to the normalized datetime.  In your example, date(julianday('2005-02-29')) is '2005-03-01' which is the correct normalized date.
>
> Therefore, a function which will validate that a string is a valid normalized date / datetime / time string is the following:
>
> datetime(julianday(x))=datetime(x)
>
> This returns 1 if the x is a valid normal date, datetime, or time and 0 if not; and, can be used as a check constraint or otherwise.
>
> sqlite> create table mytable
>     ...> (
>     ...>    data text collate nocase check (datetime(julianday(data)) = datetime(data))
>     ...> );
> sqlite> insert into mytable values('01:00');
> sqlite> insert into mytable values('24:00');
> Error: CHECK constraint failed: mytable
> sqlite> insert into mytable values('2004-02-29');
> sqlite> insert into mytable values('2005-02-29');
> Error: CHECK constraint failed: mytable
> sqlite> insert into mytable values('0000-01-29');
> sqlite> insert into mytable values('-0502-02-29');
> Error: CHECK constraint failed: mytable
> sqlite> insert into mytable values('-0502-01-29');
>
> Of course, inserting a "numeric" value also passes the test since all strictly numeric values can be converted to valid datetime strings.
>
>
>> -----Original Message-----
>> From: [hidden email] [mailto:sqlite-users-
>> [hidden email]] On Behalf Of Philip Bennefall
>> Sent: Saturday, 9 August, 2014 14:53
>> To: General Discussion of SQLite Database
>> Subject: [sqlite] Checking whether a given date is valid
>>
>> Hi all,
>>
>> I'm working on a project where I need to verify that a given date
>> actually existed (like February 29 in a particular year). I am using
>> SqLite to store a lot of data already and I didn't feel like hunting for
>> a datetime library online. I figured that there should be a way to use
>> SqLite's date functions to check this, and came up with the following
>> query:
>>
>> select 1 where date(julianday('2004-02-29'))=date('2004-02-29');
>>
>> The above query returns 1 as expected. The following one returns
>> nothing, as I would also expect:
>>
>> select 1 where date(julianday('2005-02-29'))=date('2005-02-29');
>>
>> Now, my question is simply this. Is the query sound? All the tests I
>> have run thus far have given correct results (invalid days of months,
>> leap years etc) but are there any pitfalls that I should be aware of? Is
>> it safe to rely on the conversion between the floating point julianday
>> and the date string being accurate if the date is in fact valid?
>>
>> Kind regards,
>>
>> Philip Bennefall
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> .
>

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

Re: Checking whether a given date is valid

Simon Slavin-3
In reply to this post by Philip Bennefall

On 10 Aug 2014, at 1:51am, Philip Bennefall <[hidden email]> wrote:

> That is what I tried to do in my query examples that I included in the original message. Based on those, would you say that I am doing it correctly?

I didn't figure out what you actually have stored in the database.

If you have text stored, and are trying to find things stored that aren't real dates, you should be able to do something like this:

SELECT * FROM Orders WHERE date(julianday(orderDate)) != orderDate

If you're still at the planning stage, or can revise your table, it's usually considered better to store numbers than dates.  This makes comparisons and maths faster.  In your case I'd probably store a Julian Date since you only care about date and not time.

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

Re: Checking whether a given date is valid

Keith Medcalf
In reply to this post by Philip Bennefall

Yes, either will work.  That is if you are using dates only, you can use the date function.  Similarly if you are using times only you can use the time function.  If you might have both, then you need to use the datetime function, which can validate correct time, date+time, or time only (if date only then the time is assumed to be 00:00:00, if a time only then the date is assumed to be 2000-01-01).

On Saturday, 9 August, 2014, 18:56, Philip Bennefall <[hidden email]> said:

>Hi Keith,

>Thanks for the explanation. From what I can see in your example, the
>only difference from mine is that I used date(x) and you used
>datetime(x). If I am understanding things correctly, this would give the
>same behavior if I am only concerned about dates? In this case I don't
>care about the time of day; just that the date is valid. So based on
>your description I gather that I'm doing it correctly in my example? I
>hope?

>On 8/10/2014 12:00 AM, Keith Medcalf wrote:
>> The result of converting an "invalid" datetime string with julianday()
>is a floating point julianday value which corresponds to the normalized
>datetime.  In your example, date(julianday('2005-02-29')) is '2005-03-01'
>which is the correct normalized date.
>>
>> Therefore, a function which will validate that a string is a valid
>normalized date / datetime / time string is the following:
>>
>> datetime(julianday(x))=datetime(x)
>>
>> This returns 1 if the x is a valid normal date, datetime, or time and 0
>if not; and, can be used as a check constraint or otherwise.
>>
>> sqlite> create table mytable
>>     ...> (
>>     ...>    data text collate nocase check (datetime(julianday(data)) =
>datetime(data))
>>     ...> );
>> sqlite> insert into mytable values('01:00');
>> sqlite> insert into mytable values('24:00');
>> Error: CHECK constraint failed: mytable
>> sqlite> insert into mytable values('2004-02-29');
>> sqlite> insert into mytable values('2005-02-29');
>> Error: CHECK constraint failed: mytable
>> sqlite> insert into mytable values('0000-01-29');
>> sqlite> insert into mytable values('-0502-02-29');
>> Error: CHECK constraint failed: mytable
>> sqlite> insert into mytable values('-0502-01-29');
>>
>> Of course, inserting a "numeric" value also passes the test since all
>strictly numeric values can be converted to valid datetime strings.
>>
>>
>>> -----Original Message-----
>>> From: [hidden email] [mailto:sqlite-users-
>>> [hidden email]] On Behalf Of Philip Bennefall
>>> Sent: Saturday, 9 August, 2014 14:53
>>> To: General Discussion of SQLite Database
>>> Subject: [sqlite] Checking whether a given date is valid
>>>
>>> Hi all,
>>>
>>> I'm working on a project where I need to verify that a given date
>>> actually existed (like February 29 in a particular year). I am using
>>> SqLite to store a lot of data already and I didn't feel like hunting
>for
>>> a datetime library online. I figured that there should be a way to use
>>> SqLite's date functions to check this, and came up with the following
>>> query:
>>>
>>> select 1 where date(julianday('2004-02-29'))=date('2004-02-29');
>>>
>>> The above query returns 1 as expected. The following one returns
>>> nothing, as I would also expect:
>>>
>>> select 1 where date(julianday('2005-02-29'))=date('2005-02-29');
>>>
>>> Now, my question is simply this. Is the query sound? All the tests I
>>> have run thus far have given correct results (invalid days of months,
>>> leap years etc) but are there any pitfalls that I should be aware of?
>Is
>>> it safe to rely on the conversion between the floating point julianday
>>> and the date string being accurate if the date is in fact valid?
>>>
>>> Kind regards,
>>>
>>> Philip Bennefall
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> .
>>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: Checking whether a given date is valid

Keith Medcalf
In reply to this post by Simon Slavin-3

Julian days count starting from noon Greenwich Mean Time on January 1, 4713 BC, proleptic Julian calendar.  Technically, to store a correct Julian Day floating point number you would have to take the localtime, convert it to GMT, then get the Julian Day.  When you retrieve the Julian Day, you would have to convert back to localtime after converting to calendar format.  So if you are feeding the julianday function localtime's, the julianday result is always the local julian day offset and technically does not meet the definition of "Julian Day" as known by astronomers.  Furthermore, midnight is always half-way through the previous day ... so midnight January 1, 4713 BC is JD -0.5, not 0.0.

>-----Original Message-----
>From: [hidden email] [mailto:sqlite-users-
>[hidden email]] On Behalf Of Simon Slavin
>Sent: Saturday, 9 August, 2014 19:04
>To: [hidden email]; General Discussion of SQLite Database
>Subject: Re: [sqlite] Checking whether a given date is valid
>
>
>On 10 Aug 2014, at 1:51am, Philip Bennefall <[hidden email]> wrote:
>
>> That is what I tried to do in my query examples that I included in the
>original message. Based on those, would you say that I am doing it
>correctly?
>
>I didn't figure out what you actually have stored in the database.
>
>If you have text stored, and are trying to find things stored that aren't
>real dates, you should be able to do something like this:
>
>SELECT * FROM Orders WHERE date(julianday(orderDate)) != orderDate
>
>If you're still at the planning stage, or can revise your table, it's
>usually considered better to store numbers than dates.  This makes
>comparisons and maths faster.  In your case I'd probably store a Julian
>Date since you only care about date and not time.
>
>Simon.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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