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