Re: [sqlite] Checking whether a given date is valid
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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Simon Slavin >Sent: Saturday, 9 August, 2014 19:04 >To: phi...@blastbay.com; General Discussion of SQLite Database >Subject: Re: [sqlite] Checking whether a given date is valid > > >On 10 Aug 2014, at 1:51am, Philip Bennefall 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 >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking whether a given date is valid
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 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('-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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>>> boun...@sqlite.org] 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
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> .
>>
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking whether a given date is valid
On 10 Aug 2014, at 1:51am, Philip Bennefall 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking whether a given date is valid
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('-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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
boun...@sqlite.org] 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking whether a given date is valid
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 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Updating records from a SELECT that exists in the table
Greetings! Long story... But, I have to update a DB from another DB if the record in the DB to be updated contains that record. So, with an attached DB, ATTACH 'somedb.sqlite' as client; I execute this command, BEGIN; INSERT OR REPLACE INTO LSOpenProjects SELECT * FROM client.LSOpenProjects WHERE id IN ( SELECT id from client.LSOpenProjects WHERE client.LSOpenProjects.ProjID <= 133560 AND client.LSOpenProjects.XtraB >= '2014-08-07 00:00:00'; ); END; This is great and it's working for one piece of the updates to a SharedDB, but I also want to run another command like the one above for the usersDB that will do the same, except that it should only update the id's that exists in the localDB. In other words, only update from client.LSOpenProjects, if the LSOpenProject table has that id. Imagine these two DBs: localUserDB: LSOpenProjects id,...,XtraB 20,...,'2014-04-09 10:20:34' 23,...,'2014-04-09 10:20:34' 27,...,'2014-04-09 10:20:34' 28,...,'2014-04-09 10:20:34' SharedDB: LSOpenProjects id,...,XtraB ... 20,...,'2014-08-09 14:23:14' 21,...,'2014-08-08 11:29:25' 22,...,'2014-07-09 16:12:07' 23,...,'2014-04-09 10:20:34' 24,...,'2014-08-08 23:44:11' 25,...,'2014-04-09 10:20:34' 26,...,'2014-08-09 10:00:55' 27,...,'2014-08-09 03:18:20' 28,...,'2014-04-09 10:20:34' 29,...,'2014-04-09 10:20:34' 30,...,'2014-04-09 10:20:34' The command above would return, 20 21 24 26 27 but it should only update id 20 and 27 and should not insert any into the local DB. I have no idea how to write it. I am now using two separate SQL commands and using some programming to create the updates. Any help would be greatly appreciate it. Thanks. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.8.6 beta
Both the extension entrypoints are missing from the DLL ... sqlite3_enable_load_extension sqlite3_load_extension >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of big stone >Sent: Saturday, 9 August, 2014 16:01 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] SQLite 3.8.6 beta > >hello, > >Maybe i did a wrong manipulation. >Using latest beta dll on windows / python3.3, I get this strange error : > >import sqlite3 as sqlite > File >"C:\Users\famille\Documents\winpython\WinPython-32bit-3.3.5.0\python- >3.3.5\lib\sqlite3\__init__.py", >line 23, in >from sqlite3.dbapi2 import * > File >"C:\Users\famille\Documents\winpython\WinPython-32bit-3.3.5.0\python- >3.3.5\lib\sqlite3\dbapi2.py", >line 26, in >from _sqlite3 import * >ImportError: DLL load failed: La procédure spécifiée est introuvable. > >I'm really not sure why this sudden issue, but I report as I never met >that >before. >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.8.6 beta
I believe the compile options were not set right. sqlite3_enable_load_extensions has not been exported. >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of big stone >Sent: Saturday, 9 August, 2014 16:01 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] SQLite 3.8.6 beta > >hello, > >Maybe i did a wrong manipulation. >Using latest beta dll on windows / python3.3, I get this strange error : > >import sqlite3 as sqlite > File >"C:\Users\famille\Documents\winpython\WinPython-32bit-3.3.5.0\python- >3.3.5\lib\sqlite3\__init__.py", >line 23, in >from sqlite3.dbapi2 import * > File >"C:\Users\famille\Documents\winpython\WinPython-32bit-3.3.5.0\python- >3.3.5\lib\sqlite3\dbapi2.py", >line 26, in >from _sqlite3 import * >ImportError: DLL load failed: La procédure spécifiée est introuvable. > >I'm really not sure why this sudden issue, but I report as I never met >that >before. >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.8.6 beta
complementary information : - compiling manually sqlite.dll , I have no issue, - so the problem, if problem, is the official Sqlite.dll downloadble from sqlite.org. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking whether a given date is valid
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('-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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] 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
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.8.6 beta
hello, Maybe i did a wrong manipulation. Using latest beta dll on windows / python3.3, I get this strange error : import sqlite3 as sqlite File "C:\Users\famille\Documents\winpython\WinPython-32bit-3.3.5.0\python-3.3.5\lib\sqlite3\__init__.py", line 23, in from sqlite3.dbapi2 import * File "C:\Users\famille\Documents\winpython\WinPython-32bit-3.3.5.0\python-3.3.5\lib\sqlite3\dbapi2.py", line 26, in from _sqlite3 import * ImportError: DLL load failed: La procédure spécifiée est introuvable. I'm really not sure why this sudden issue, but I report as I never met that before. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking whether a given date is valid
On 9 Aug 2014, at 9:53pm, Philip Bennefall 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] archive of database
My recommendation is to dump the SQLite database into a corresponding .sql file, and then compress that ASCII file using your favorite compressor (ZIP, RAR, etc.) In my view, this method has two advantages over copying the actual binary file for backup purposes, but no obvious disadvantages. 1. Compression ratio is always (at least with my own tests) significantly better on the ASCII .sql file than the SQLite binary equivalent. So, you need less storage for your backups, or less bandwidth for transmitting the database. 2. In case future versions of SQLite use a different incompatible format, your backups from long-long ago will still be able to rebuild the database using the current version of SQLite. Even in the unlikely event, some source level incompatibilities appear, you can easily edit those in the ASCII text file using a plain text editor, whereas the trying to convert the binary to a compatible format is not necessarily a trivial job, assuming you no longer have access to the needed version of SQLite. -Original Message- From: Levente Kovacs Sent: Saturday, August 09, 2014 2:18 PM To: sqlite-users@sqlite.org Subject: [sqlite] archive of database Dear List, Is it a recommended method to archive or copy a database just copying the sqlite file, or should I make a dump of the database, and move the SQL statements? I mean... is SQLite designed to support the move of the sqlite file? What if a new version of the library comes out? Will it be able to read older version of database? What is the most portable way? Thanks, Levente -- Levente Kovacs CTO, CSO http://levente.logonex.eu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] archive of database
On 9 Aug 2014, at 12:18pm, Levente Kovacs wrote: > I mean... is SQLite designed to support the move of the sqlite file? Yes. If the database was closed properly, all your data is in one file. SQLite does not care where file is: you can move it from one folder or disk to another without problems. > What if > a new version of the library comes out? Will it be able to read older > version of database? SQLite 3 was introduced ten years ago. The current version of SQLite 3 can read/write files made using SQLite 3.0.0 in 2004. There are no plans to change this until SQLite 4 comes out, some time in the misty future, at which time SQLite 3 will still be available. The opposite is not true: SQLite 3.0.0 cannot read many files made by SQLite 3.8.5. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong?
On 8-8-2014 23:57, Errol Emden wrote: The SQL script you wrote actually provides the same information as mine - it lists all movies that Julie Andrews is in but it does NOT provide who is the leading actor in each movie, as all names selected is that of Julie Andrews. must have missed that ;-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] archive of database
Dear List, Is it a recommended method to archive or copy a database just copying the sqlite file, or should I make a dump of the database, and move the SQL statements? I mean... is SQLite designed to support the move of the sqlite file? What if a new version of the library comes out? Will it be able to read older version of database? What is the most portable way? Thanks, Levente -- Levente Kovacs CTO, CSO http://levente.logonex.eu ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
