[sqlite] best way to match a date but not a time?
Hi, I'm very new to SQLite, and I'm using it with Python. I want to have queries that will match dates but not care about times. The date might be today, anything within the last week, month, year, or a range of dates. I'm using Python's datetime function, so the dates enter the database in this format 2007-09-01 12:00:02. So far, or queries seem to work, like: SELECT duration FROM specactivities WHERE date 2006 but what I can't do is use =, since it seems like it is trying to match both the date and the exact time. Any help is appreciated.
Re: [sqlite] best way to match a date but not a time?
On 02/09/07, C M [EMAIL PROTECTED] wrote:
Hi, I'm very new to SQLite, and I'm using it with Python.
I want to have queries that will match dates but not care about times.
.
.
.
Hi,
If your dates are stored in julian day (real) format, then the
fractional part will indicate the time of day. This can be removed
with the 'round' function.
Thus:
sqlite create table tstTbl( a integer primary key, tstDate real );
sqlite
sqlite insert into tstTbl( tstDate ) values( julianday('now') );
sqlite insert into tstTbl( tstDate ) values( julianDay('2007-10-01
12:12:12' ));
sqlite insert into tstTbl( tstDate ) values( julianDay('2007-11-01
12:13:12' ));
sqlite insert into tstTbl( tstDate ) values( julianDay('2007-10-01
12:15:12' ));
sqlite insert into tstTbl( tstDate ) values( julianDay('2007-10-01
12:14:12' ));
sqlite insert into tstTbl( tstDate ) values( julianDay('2007-10-01
12:14:13' ));
sqlite insert into tstTbl( tstDate ) values( julianDay('2007-10-01
11:14:13' ));
sqlite insert into tstTbl( tstDate ) values( julianDay('2007-10-01
13:14:13' ));
sqlite insert into tstTbl( tstDate ) values( julianDay('2007-10-01
14:14:13' ));
sqlite
sqlite select *, dateTime( tstDate ) from tstTbl order by tstDate;
1|2454345.90499024|2007-09-02 09:43:11
7|2454374.96820602|2007-10-01 11:14:13
2|2454375.00847222|2007-10-01 12:12:12
5|2454375.00986111|2007-10-01 12:14:12
6|2454375.00987269|2007-10-01 12:14:13
4|2454375.0106|2007-10-01 12:15:12
8|2454375.05153935|2007-10-01 13:14:13
9|2454375.09320602|2007-10-01 14:14:13
3|2454406.00916667|2007-11-01 12:13:12
sqlite
sqlite select *, dateTime( tstDate ) from tstTbl where
...cast(round(tstDate) as integer)=
...cast(round(julianday('2007-11-01')) as integer);
3|2454406.00916667|2007-11-01 12:13:12
sqlite
sqlite select *, dateTime( tstDate ) from tstTbl where
...cast(round(tstDate) as integer)=
...cast(round(julianday('2007-10-01')) as integer);
2|2454375.00847222|2007-10-01 12:12:12
4|2454375.0106|2007-10-01 12:15:12
5|2454375.00986111|2007-10-01 12:14:12
6|2454375.00987269|2007-10-01 12:14:13
7|2454374.96820602|2007-10-01 11:14:13
8|2454375.05153935|2007-10-01 13:14:13
9|2454375.09320602|2007-10-01 14:14:13
sqlite
sqlite select *, dateTime( tstDate ) from tstTbl where
...cast(round(tstDate) as integer)=
...cast(round(julianday('2007-09-02')) as integer);
1|2454345.90499024|2007-09-02 09:43:11
sqlite
Rgds,
Simon
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] best way to match a date but not a time?
I want to have queries that will match dates but not care about times. The date might be today, anything within the last week, month, year, or a range of dates. I'm using Python's datetime function, so the dates enter the database in this format 2007-09-01 12:00:02. So far, or queries seem to work, like: SELECT duration FROM specactivities WHERE date 2006 but what I can't do is use =, since it seems like it is trying to match both the date and the exact time. http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions You're storing dates as TEXT, not DOUBLE, correct? WHERE date(colName) = '2007-09-01' should work to match a particular date. Be aware though, this approach disables the use of indices. So, if you have an index that will be used with or queries you mentioned before, the specific date-match with date(colName) will be slower because it has to do a full table scan. Perhaps this would be better: SELECT duration FROM specactivities WHERE date = '2007-09-01' AND date '2007-09-02' To the experts: will an index be used for both comparisons in the WHERE clause? Or just the first? I think I remember reading somewhere that an index can be used for any number of exact matches, but only 1 less-than or greater-than comparison, and that would be the last usable column of the index. Or maybe it could be used for = AND on the same column at the same time, but that would be the last usable column of the index? HTH, Trey - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] best way to match a date but not a time?
On Sun, 2007-09-02 at 11:13 -0400, Trey Mack wrote: I want to have queries that will match dates but not care about times. The date might be today, anything within the last week, month, year, or a range of dates. I'm using Python's datetime function, so the dates enter the database in this format 2007-09-01 12:00:02. So far, or queries seem to work, like: SELECT duration FROM specactivities WHERE date 2006 but what I can't do is use =, since it seems like it is trying to match both the date and the exact time. http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions You're storing dates as TEXT, not DOUBLE, correct? WHERE date(colName) = '2007-09-01' should work to match a particular date. Be aware though, this approach disables the use of indices. So, if you have an index that will be used with or queries you mentioned before, the specific date-match with date(colName) will be slower because it has to do a full table scan. Perhaps this would be better: SELECT duration FROM specactivities WHERE date = '2007-09-01' AND date '2007-09-02' To the experts: will an index be used for both comparisons in the WHERE clause? Or just the first? I think I remember reading somewhere that an index can be used for any number of exact matches, but only 1 less-than or greater-than comparison, and that would be the last usable column of the index. Or maybe it could be used for = AND on the same column at the same time, but that would be the last usable column of the index? Correct. This kind of query is efficient. Think of the index as a sorted list of dates. SQLite finds the first entry in the index where (date='2007-09-01'), then scans linearly until it finds the first records where (date='2007-09-02'). Then stops. Dan. HTH, Trey - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] best way to match a date but not a time?
Thanks, Simon, Trey, and Dan, this is really helpful and has got me back on track. -CM On 9/2/07, Trey Mack [EMAIL PROTECTED] wrote: I want to have queries that will match dates but not care about times. The date might be today, anything within the last week, month, year, or a range of dates. I'm using Python's datetime function, so the dates enter the database in this format 2007-09-01 12:00:02. So far, or queries seem to work, like: SELECT duration FROM specactivities WHERE date 2006 but what I can't do is use =, since it seems like it is trying to match both the date and the exact time. http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions You're storing dates as TEXT, not DOUBLE, correct? WHERE date(colName) = '2007-09-01' should work to match a particular date. Be aware though, this approach disables the use of indices. So, if you have an index that will be used with or queries you mentioned before, the specific date-match with date(colName) will be slower because it has to do a full table scan. Perhaps this would be better: SELECT duration FROM specactivities WHERE date = '2007-09-01' AND date '2007-09-02' To the experts: will an index be used for both comparisons in the WHERE clause? Or just the first? I think I remember reading somewhere that an index can be used for any number of exact matches, but only 1 less-than or greater-than comparison, and that would be the last usable column of the index. Or maybe it could be used for = AND on the same column at the same time, but that would be the last usable column of the index? HTH, Trey - To unsubscribe, send email to [EMAIL PROTECTED] -
