How can I select entries for a given weekday using SQL?

Question

I could use this query to select all orders with a date on a monday:

SELECT * from orders WHERE strftime("%w", date)="1";

But as far as I know, this can't be speed up using an index, as for every row strftime has to be calculated.

I could add an additional field with the weekday stored, but I want to avoid it. Is there a solution that makes use of an index or am I wrong and this query actually works fine? (That means it doesn't have to go through every row to calculate the result.)

Answer 1

If you want all Mondays ever, you'd need a field or sequential scan. What you could do, is calculate actual dates for example for all Mondays within a year. The condition WHERE date IN ('2009-03-02', '2009-02-23', ...) would use index

Answer 2

Or as an alternative to vartec's suggestion, construct a calendar table consisting only of a date and a day name for each day in the year (both indexed) and then perform your query by doing a JOIN against this table.