Add filter for 'Day of week' on Date / DateTime fields
|Reported by:||Ross Poulton||Owned by:||nobody|
|Component:||Database layer (models, ORM)||Version:||master|
|Cc:||ross@…||Triage Stage:||Design decision needed|
|Has patch:||yes||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||yes|
It would be very useful to be able to do DB filtering based on the day of week (eg Mon, Tue, Wed). For example:
would result in SQL similar to:
SELECT [select query etc here] WHERE DAYOFWEEK(event_date) = 3;
SELECT [select query etc here] WHERE EXTRACT(DOW FROM event_date) = 3;
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.
Change History (19)
comment:1 Changed 9 years ago by
|Patch needs improvement:||set|
|Triage Stage:||Unreviewed → Design decision needed|