Angus March wrote:
> Igor Tandetnik wrote:
>> Angus March wrote:
>>
>>> I have a table where I need to record the date of each insert.
>>> Sometime later I'll then delete all rows that were inserted more
>>> than 90 days ago. Is it possible to do this w/out performing a
>>> table scan?
>>>
>>
>> Create an index on the date column.
>>
>
>    You're sure that's all there is to it? I haven't been able to make
> use of the EXPLAIN results.

Have you tried EXPLAIN QUERY PLAN?

>    Also, I don't know how to specify a date based on what would be an
> INTERVAL in MySQL.

SQLite doesn't have a dedicated data type for representing dates or 
times. You can choose your own format. Some are better supported than 
others by built-in date/time functions: 
http://www.sqlite.org/lang_datefunc.html

For the sake of argument, let's assume you've chosen to represent your 
dates as strings in the form YYYY-MM-DD. Note how the usual string 
lexicographical comparison also orders dates correctly.

> How do you express dates that are older than 90
> days ago?

WHERE mydatecol < date('now', '-90 days')

Igor Tandetnik 



_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to