I have a table (called 'entry') which has a datetime column (called 'access_date'), and I want to do an SQLAlchemy query that only produces results where entry.access_date is a Monday (or any other day of the week specified by a number [0..6]).
Is this possible? I am using sqlite & SQLalchemy 0.5.8 if that makes any difference.
I'm not an expert on SQLAlchemy, so await others' views. I've adapted an example from http://www.sqlalchemy.org/docs/ormtutorial.html#common-filter-operators. This may be worth testing & experimenting with while you wait for others' answers.:
query.filter(entry.access_date.in_([0,1,2,3,4,5,6]))
There is no generic DAYOFWEEK() function supported by SQLAlchemy, so you will have to use dialect-specific sql in the where clause.
For MySQL, you would go with custom func 'weekday' or 'dayofweek', but since sqlite has neither datetime type nor weekday()/dayofweek(), you need some raw sql there.
http://www.mail-archive.com/[email protected]/msg51116.html here are examples for this query.
In SQLA, this will look like
query.filter("strftime('%w', access_date) = :dow").params(dow=0).all()
Further from Daniel Kluev's answer, I found another way of saying the same thing (possibly nicer looking?)
query.filter(func.strftime('%w', Entry.access_date) == str(weekday)).all()
Where weekday is a number [0..6]