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