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