Django date filter by day of month

Question

I need to write a query that returns all object less that or equal to a certain day of a certain month. The year is not important. It's easy enough to get an object by a particular day/month (assume now = datetime.datetime.now()):

posts = TodaysObject.objects.filter(publish_date__day=now.day, publish_date__month=now.month)

But I can't do this:

posts = TodaysObject.objects.filter(publish_date__day__lte=now.day, publish_date__month=now.month)

Seems that Django thinks I'm trying to do a join when combining multiple field lookups (publish_date__day__lte). What's the best way to do this in Django?

Answer 1

Try this:

Option 1:

from django.db.models import Q

datafilter = Q()
for i in xrange(1, now.day+1):
    datafilter = datafilter | Q(publish_date__day=i)
datafilter = datafilter & Q(publish_date__month=now.month)
posts = TodaysObject.objects.filter(datafilter)

Option 2:

Perform raw sql query:

def query_dicts(query_string, *query_args):
    from django.db import connection
    cursor = connection.cursor()
    cursor.execute(query_string, query_args)
    col_names = [desc[0] for desc in cursor.description]
    while True:
        row = cursor.fetchone()
        if row is None:
            break
        row_dict = dict(izip(col_names, row))
        yield row_dict
    return
posts = query_dicts('SELECT * FROM tablename WHERE DAY(publish_date)<=%s AND MONTH(publish_date)=%s', now.day, now.month)

Using extra() function:

posts = TodaysObject.objects.extra([where='DAY(publish_date)<=%d AND MONTH(publish_date)=%d' % (now.day, now.month)])

It's assumed that you are using MySQL. For PostgreSQL, you need to change DAY(publish_date) and MONTH(publish_date) to DATE_PART('DAY', publish_date) and DATE_PART('MONTH', publish_date) respectively.

Answer 2

it's not always portable from one database engine to another, but you may want to look into the extra() queryset method.

from django docs

this allows you to inject raw sql to construct more complex queries than the django queryset api.

if your application needs to be portable to different database engines, you can try restructuring so you have day, month, and year integer fields.