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] -----------------------------------------------------------------------------
