Proposal: Day of Week Filter in ORM on Date / DateTime Fields

200 views
Skip to first unread message

Ross Poulton

unread,
Sep 29, 2008, 9:37:02 PM9/29/08
to Django developers
When working with any calendar or event tracking information in Django
it would be useful to be able to filter for items based on day of
week:

Event.objects.filter(event_date__week_day=1) # objects from any
month/year/date, provided it's a Sunday

will generate:

SELECT ... WHERE EXTRACT('dow' FROM pub_date) = '1'; # Syntax
varies on DB engine used.

The most common use case for this is probably recurring events, so it
won't be used by _everybody_ but it's a relatively simple change to
implement.

I've done most of the footwork and investigation for this, a patch is
attached to ticket #7672 [1]. It hasn't actually been updated now
since pre-1.0, so I'm cleaning it up now and should have a new version
shortly. Between my patch & 1.0 were no major ORM changes that I'm
aware of, so it'll be incidentals such as docs & tests that will need
a real cleanup. AFAIK the code works on MySQL, Postgres & SQLite, and
needs somebody to test on Oracle for it to be complete.

Any thoughts on this? The only real alternative is to drop to raw SQL
in a calendar app - but if we can exploit functionality within the
database to do it cleanly in the ORM, why not?

The only potential pitfall I'm aware of is with weeks starting on
different days, see ticket #1061 [2]. I'm not intimate with the
details of that ticket so can't really comment on it at this stage.

Ross

[1] http://code.djangoproject.com/ticket/7672
[2] http://code.djangoproject.com/ticket/1061

Ross Poulton

unread,
Sep 29, 2008, 10:12:14 PM9/29/08
to Django developers
My latest patch added to ticket # 7672 works against the latest SVN
checkout (r9097).

All it needs in my opinion is a decision on the best way to handle
Oracle dates, and testing against Oracle.

Ross

Malcolm Tredinnick

unread,
Sep 29, 2008, 10:29:06 PM9/29/08
to django-d...@googlegroups.com

On Mon, 2008-09-29 at 18:37 -0700, Ross Poulton wrote:
> When working with any calendar or event tracking information in Django
> it would be useful to be able to filter for items based on day of
> week:
>
> Event.objects.filter(event_date__week_day=1) # objects from any
> month/year/date, provided it's a Sunday
>
> will generate:
>
> SELECT ... WHERE EXTRACT('dow' FROM pub_date) = '1'; # Syntax
> varies on DB engine used.
>
> The most common use case for this is probably recurring events, so it
> won't be used by _everybody_ but it's a relatively simple change to
> implement.
>
> I've done most of the footwork and investigation for this, a patch is
> attached to ticket #7672 [1]. It hasn't actually been updated now
> since pre-1.0, so I'm cleaning it up now and should have a new version
> shortly. Between my patch & 1.0 were no major ORM changes that I'm
> aware of, so it'll be incidentals such as docs & tests that will need
> a real cleanup. AFAIK the code works on MySQL, Postgres & SQLite, and
> needs somebody to test on Oracle for it to be complete.

I'm personally a bit "meh" on this idea, although I can see it's useful
for some number of people > 1. There's no such thing as a "simple
change". I suspect the pros and cons pretty nicely balance each other
out.

I've been thinking through, with a couple of different people, how we
can support extra lookup types on fields and so I'm going to reserve
judgement on this particular implementation until I have some thoughts
in order on the latter subject. Maybe you'll convince another maintainer
in the interim, but it's probably a good start to get the patch up to
date and then drop it into the 1.1 features list, since running, tested
code is always a good start for assessing something.

>
> Any thoughts on this? The only real alternative is to drop to raw SQL
> in a calendar app - but if we can exploit functionality within the
> database to do it cleanly in the ORM, why not?
>
> The only potential pitfall I'm aware of is with weeks starting on
> different days, see ticket #1061 [2]. I'm not intimate with the
> details of that ticket so can't really comment on it at this stage.

If you're writing an API for talking to the data storage layer, pick a
value for the first day that is consistent at the Django level. People
handling localised dates with different start dates will have to do the
conversion before making it a filter. When Django adds support for that
kind of stuff, we might well end up with some utility functions if this
type of filter goes into the ORM. Don't create extra problems for
yourself here by inentionally letting presentation information leak into
the ORM layer.

Regards,
Malcolm

Ross Poulton

unread,
Sep 29, 2008, 11:36:52 PM9/29/08
to Django developers

On Sep 30, 12:29 pm, Malcolm Tredinnick <malc...@pointy-stick.com>
wrote:
> I've been thinking through, with a couple of different people, how we
> can support extra lookup types on fields and so I'm going to reserve
> judgement on this particular implementation until I have some thoughts
> in order on the latter subject. Maybe you'll convince another maintainer
> in the interim, but it's probably a good start to get the patch up to
> date and then drop it into the 1.1 features list, since running, tested
> code is always a good start for assessing something.

It'd be great to have some sort of drop-in architecture to allow new
query types that can be defined perhaps by an installed application,
rather than having to modify the Django distribution.

That said, since such a beast doesn't currently exist, I've just
updated my code to work on a current SVN checkout of Django. From my
POV the only thing missing is testing on Oracle, although I appreciate
that other viewpoints may see it wildly differently :)

> If you're writing an API for talking to the data storage layer, pick a
> value for the first day that is consistent at the Django level.... Don't
> create extra problems for yourself here by inentionally letting
> presentation information leak into the ORM layer.

Good point - the code currently attached to the ticket will always
force Sunday=1, Saturday=7. This happens regardless of the database
backend in use.

Thanks for your feedback Malcolm, I'll leave it here for the moment to
see what comments other contributors may have.

Regards,
Ross

Ivan Sagalaev

unread,
Sep 30, 2008, 2:45:39 AM9/30/08
to django-d...@googlegroups.com
Malcolm Tredinnick wrote:
> I've been thinking through, with a couple of different people, how we
> can support extra lookup types on fields and so I'm going to reserve
> judgement on this particular implementation until I have some thoughts
> in order on the latter subject.

In my opinion this patch doesn't make any difference for any new
architecture of lookup types because it doesn't change the current one.
It just adds another item to a fixed list of supported lookup types and
several conditions leaving them as uniform as they are.

Reply all
Reply to author
Forward
0 new messages