Add filter for 'Day of week' on Date / DateTime fields

[Ross Poulton]

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

[Ross Poulton]

Initial Patch - Allow day of week filtering in ORM.

[Marc Garcia]

It looks nice to me, but as improvement this is post 1.0.

Some comments on the patch:

• dow sounds meaningless to me, shouldn't be better week_day?
• postgresql and sqlite backends are not modified. Am I missing something, or them should be also changed?
• tests and documentation should be added to the patch.

Btw, good idea, and good work. :)

[Ross Poulton]

New revision, see note below.

[Ross Poulton]

Added attachment dayofweek_filter-r7866.v2.diff :

• Renamed dow to week_day (I'd been using dow as that's what Postgres uses, and was my initial use case.
• Postgres backend needed to be modified to translate week_day into dow for the query.
• SQLite backend still unmodified, got to get my head around how the existing date filtering works there.
• Oracle not yet tested
• Tests & Docs added

Post 1.0 sounds fine to me, thanks for the suggestions. I knew I'd miss a bunch of stuff with my first patch.

[Ross Poulton]

For reference, I've just done some investigation on the different date structures used in Python & the various database engines. I'm beginning to see why this isn't as easy as at face value :)

Method                              Range
------                              -----
PYTHON
    datetime_object.weekday()       0-6    Sunday=6
    datetime_object.isoweekday()    1-7    Sunday=7
    dt_object.isoweekday() % 7      0-6    Sunday=0 # Can easily add 1 for a 1-7 week where Sunday=1

MYSQL
    DAYOFWEEK(timestamp)            1-7    Sunday=1
    WEEKDAY(timestamp)              0-6    Monday=0

POSTGRES
    EXTRACT('dow' FROM timestamp)   0-6    Sunday=0
    TO_CHAR(timestamp, 'D')         1-7    Sunday=1

ORACLE
    TO_CHAR(timestamp, 'D')         1-7    Sunday=1 (US), Sunday=6 (UK)

I'm thinking it will be easier to assume 1-7 with Sunday=1 and use the relevant functions from above, although I'm unsure at this stage of the best way to handle Oracle as I don't have access to an Oracle server to test on. Do we know the current Oracle date locale config from within Django at runtime?

[Malcolm Tredinnick]

@rossp: that sort of analysis is exactly what needed to be done here. Thanks for taking the time to do the research (if you could find a way to pass on that diligence to other people wanting database changes, it would be appreciated). Looks like we can pick a way for each database that will return something consistent (e.g. "Sunday" is 1).

Not sure how we find out the Oracle date locale. If it's a client-side settings, it's something we can control. If it's a server-side setting (which sounds more likely), it's something we would need to be able to query. Ian Kelly might be able to help us out there. I can't imagine the answer would be "it's impossible", though.

Probably still post-1.0, barring miracles or lots of free time, but it's looking like something that might be possible.

[Ross Poulton]

Malcolm, thanks for the positive feedback. No point trying to make a change like this unless it works (and, indeed, works consistently) across all of the officially supported database systems.

Current Status:

• Settled on Sunday=1, Saturday=6. IMO this is the only logical option after reviewing the above table, and regardless of an individuals locale settings it's at least consistent within the API.
• Works in Postgres & MySQL
• Sort-of works in SQLite, working on that (I'm having other unrelated SQLite issues locally)

I will attempt contacting Ian Kelly re: Oracle. Latest patch is attached.

[Ross Poulton]

3rd revision. Makes Sunday=1, Saturday=6. Works in Postgres & MySQL, SQLite & Oracle untested.

[Ross Poulton]

Fixed indentation, now works in sqlite3, postgres and mysql. Oracle untested.

[Ross Poulton]

Just added another patch:

• Fixed indentation so it now works in sqlite3
• Oracle still untested. Have e-mailed Ian Kelly, although if anybody else out there uses Oracle it'd be great if they could test this :)

Test suites have been updated, so in theory you can just patch this in and run the Django test suite.

[Ross Poulton]

Force oracle to use American format week day (i.e. Sunday=1)

[Ross Poulton]

Attached another file, this one forces Oracle to use the American territory information, including Sunday=1.

I'm not sure that this is the best way to do it, but it avoids doing another query at runtime. Another option, suggested by Ian Kelly, is to do a query at connection time and cache the resulting offset. This removes reliance on knowledge of Oracle's territories:

select to_char(to_date('06-JUL-2008', 'DD-MON-YYYY'), 'D') as 'testday' from dual;

If the resulting 'testday' field is 1, do nothing. If it's 7, treat dates like Python dates (eg " Monday-base-date % 7 + 1 = sunday-base-date ").

For now I'm going to leave this one as-is until someone can confirm it works with Oracle, unless the devs believe we should test this offset. It will result in an extra query for each connection, I'm not sure if that's a problem or not.

[Ross Poulton]

Updated to work on Django 1.0 (SVN rev 9097)

[Ross Poulton]

I have added this to the 1.1 feature request list, with discussion at in ​django-developers.

[Karen Tracey]

I updated the patch to apply cleanly on current trunk, then tested on MySQL, Oracle, PostgreSQL (8.3), and sqlite.

The first two worked fine, the new tests that exercise week_day lookup passed.

PostgreSQL failed due to lack of an operator comparing text to integer and lack of an explicit cast. I think this extra pickiness on Postgres is due to the version I am running, and I found I could fix it by adding an explicit cast to integer: return "TO_CHAR(%s, 'D')::integer" % field_name, which looks a little odd (to_char cast to integer?) but works.

Sqlite also failed, it just didn't find matching records, until I removed the conversion to unicode that had surrounded the return value in _sqlite_extract: return (dt.isoweekday() % 7) + 1. This one I don't understand, the full function here is:

def _sqlite_extract(lookup_type, dt):
    if dt is None:
        return None
    try:
        dt = util.typecast_timestamp(dt)
    except (ValueError, TypeError):
        return None
    if lookup_type == 'week_day':
        return (dt.isoweekday() % 7) + 1
    else:
        return unicode(getattr(dt, lookup_type))

It isn't immediately clear to me why the unicode conversion that used to be there used to work and now does not, and unfortunately I'm running out of time to look at this today (I thought I was going to have much more time to work on this today but the untimely death of a power supply rather ate up my day) so figured I'd just note this here and see if it makes sense to anyone else. Ross -- this did used to work on sqlite3 with the unicode conversion, correct?

[Karen Tracey]

OK, I think I figured out what was causing the sqlite failures. For month and day lookups (but not week_day), get_db_prep_lookup was forcing the rhs-value to unicode. This was added in r8526 so wasn't in the code base when the patch was initially developed. Adding week_day to the list of lookups here means _sqlite_extract can consistently return unicode for week_day as well as the other lookups, plus the need for the ::integer cast in the Postgres code goes away, so that seems like a cleaner solution. I updated the patch to use this approach.

[Karen Tracey]

(In [9818]) Fixed #7672 -- Added a 'week_day' lookup type. Many thanks to Ross Poulton for the proposal and implementation on all built-in database backends..

[(none)]

Milestone post-1.0 deleted