id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
7672	Add filter for 'Day of week' on Date / DateTime fields	Ross Poulton	nobody	"It would be very useful to be able to do DB filtering based on the day of week (eg Mon, Tue, Wed). For example:

{{{ Event.objects.filter(event_date__dow=3) }}}

would result in SQL similar to:

MySQL: {{{ SELECT [select query etc here] WHERE DAYOFWEEK(event_date) = 3; }}}

PostgreSQL: {{{ SELECT [select query etc here] WHERE EXTRACT(DOW FROM event_date) = 3; }}}

Oracle: {{{ SELECT [select query etc here] WHERE TO_CHAR(event_date,'D') = '3'; }}}

I have tested the syntax of MySQL's DAYOFWEEK() and Postgres' EXTRACT(DOW) functions. I don't have access to Oracle to check the syntax of the above queries. I assume SQLite will need a wrapper function similar to what's used for {{{__month }}} et al right now.

Unsure at this stage of best way to handle different locales where the start date may change between Sunday/Monday/other, or different database systems assuming a week start day.

The attached patch is my intitial draft. It has been tested in both MySQL 5.0 & PostgreSQL 7.4. Oracle is untested, and I haven't yet figured out the SQLite code. I have a concern the Oracle code will bomb out due to TO_CHAR returning a char, and the Django ORM tries to compare it to an integer. 

'''References:'''

MySQL Date Ref: http://mysql-tips.blogspot.com/2005/04/mysql-date-calculations.html

Postgres: http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html

Oracle: http://download.oracle.com/docs/cd/B28359_01/olap.111/b28126/dml_functions_2113.htm"		closed	Database layer (models, ORM)	dev		fixed		ross@…	Design decision needed	1	0	0	1	0	0