Code

Opened 6 years ago

Closed 5 years ago

Last modified 5 years ago

#7672 closed (fixed)

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

Reported by: rossp Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords:
Cc: ross@… Triage Stage: Design decision needed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: UI/UX:

Description

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

Attachments (7)

dayofweek_filter-r7866.diff (3.5 KB) - added by rossp 6 years ago.
Initial Patch - Allow day of week filtering in ORM.
dayofweek_filter-r7866.v2.diff (6.8 KB) - added by rossp 6 years ago.
New revision, see note below.
dayofweek_filter.r7875.v3.diff (7.6 KB) - added by rossp 6 years ago.
3rd revision. Makes Sunday=1, Saturday=6. Works in Postgres & MySQL, SQLite & Oracle untested.
dayofweek_filter.r7875.v4.diff (7.6 KB) - added by rossp 6 years ago.
Fixed indentation, now works in sqlite3, postgres and mysql. Oracle untested.
dayofweek_filter.r7875.r4.diff (8.5 KB) - added by rossp 6 years ago.
Force oracle to use American format week day (i.e. Sunday=1)
dayofweek_filter.r9097.r5.diff (8.5 KB) - added by rossp 6 years ago.
Updated to work on Django 1.0 (SVN rev 9097)
7672.diff (8.9 KB) - added by kmtracey 5 years ago.

Download all attachments as: .zip

Change History (19)

Changed 6 years ago by rossp

Initial Patch - Allow day of week filtering in ORM.

comment:1 Changed 6 years ago by garcia_marc

  • milestone set to post-1.0
  • Needs documentation set
  • Needs tests set
  • Patch needs improvement set
  • Triage Stage changed from Unreviewed to Design decision needed

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. :)

Changed 6 years ago by rossp

New revision, see note below.

comment:2 Changed 6 years ago by rossp

  • Needs documentation unset
  • Needs tests unset

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.

comment:3 Changed 6 years ago by rossp

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?

comment:4 Changed 6 years ago by mtredinnick

@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.

comment:5 Changed 6 years ago by rossp

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.

Changed 6 years ago by rossp

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

Changed 6 years ago by rossp

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

comment:6 Changed 6 years ago by rossp

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.

Changed 6 years ago by rossp

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

comment:7 Changed 6 years ago by rossp

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.

Changed 6 years ago by rossp

Updated to work on Django 1.0 (SVN rev 9097)

comment:8 Changed 6 years ago by rossp

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

comment:9 Changed 5 years ago by kmtracey

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?

comment:10 Changed 5 years ago by kmtracey

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.

Changed 5 years ago by kmtracey

comment:11 Changed 5 years ago by kmtracey

  • Resolution set to fixed
  • Status changed from new to closed

(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..

comment:12 Changed 5 years ago by anonymous

  • milestone post-1.0 deleted

Milestone post-1.0 deleted

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.