Filter models by checking one datetime's month against another

Question

Using Django's ORM, I am trying to find instances of myModel based on two of its datetime variables; specifically, where the months of these two datetimes are not equal. I understand to filter by the value of a modelfield, you can use Django's F( ) expressions, so I thought I'd try something like this:

myModel.objects.filter(fixed_date__month=F('closed_date__month'))

I know this wouldn't find instances where they aren't equal, but I thought it'd be a good first step since I've never used the F expressions before. However, it doesn't work as I thought it should. I expected it to give me a queryset of objects where the value of the fixed_date month was equal to the value of the closed_date month, but instead I get an error:

FieldError: Join on field 'closed_date' not permitted. Did you misspell 'month' for the lookup type?

I'm not sure if what I'm trying to do isn't possible or straightforward with the ORM, or if I'm just making a simple mistake.

Answer 1

It doesn't look like django F objects currently support extracting the month inside a DateTimeField, the error message seems to be stating that the F object is trying to convert the '__' inside the string 'closed_date__month' as a Foreignkey between different objects, which are usually stored as joins inside an sql database.

You could carry out the same query by iterating across the objects:

result = []
for obj in myModel.objects.all():
    if obj.fixed_date.month != obj.closed_date.month:
        result.append(obj)

or as a list comprehension:

result = [obj for obj in myModel.objects.all() if obj.fixed_date.month != obj.closed_date.month]

Alternatively, if this is not efficient enough, the months for the two dates could be cached as IntegerFields within the model, something like:

 class myModel(models.Model):
     ....other fields....
     fixed_date = models.DateTimeField()
     closed_date = models.DateTimeField()
     fixed_month = models.IntegerField()
     closed_month = models.IntegerField()

store the two integers when the relevant dates are updated:

myModel.fixed_month = myModel.fixed_date.month
myModel.save()

Then use an F object to compare the two integer fields:

myModel.objects.filter(fixed_month__ne=F('closed_month'))

The ne modifier will do the not equal test.

Edit - using raw sql

If you are using an sql based database, then most efficient method is to use the .raw() method to manually specify the sql:

myModel.objects.raw('SELECT * FROM stuff_mymodel WHERE MONTH(fixed_date) != MONTH(close_date)')

Where 'stuff_mymodel' is the correct name of the table in the database. This uses the SQL MONTH() function to extract the values from the month fields, and compare their values. It will return a collection of objects.

There is some nay-saying about the django query system, for example: http://charlesleifer.com/blog/shortcomings-in-the-django-orm-and-a-look-at-peewee-a-lightweight-alternative/. This example could be taken as demonstrating another inconsistency in it's query api.

Answer 2

My thinking is this:

class myModel(models.Model):
    fixed_date = models.DateTimeField()
    closed_date = models.DateTimeField()

    def has_diff_months(self):
        if self.fixed_date.month != self.closed_date.month:
            return True
        return False

Then:

[x for x in myModel.objects.all() if x.has_diff_months()]

However, for a truly efficient solution you'd have to use another column. It makes sense to me that it'd be a computed boolean field that is created when you save, like so:

class myModel(models.Model):
    fixed_date = models.DateTimeField()
    closed_date = models.DateTimeField()
    diff_months = models.BooleanField()

    #overriding save method
    def save(self, *args, **kwargs):
        #calculating the value for diff_months
        self.diff_months = (self.fixed_date.month != self.closed_date.month)
        #aaand... saving:              
        super(Blog, self).save(*args, **kwargs)

Then filtering would simply be:

myModel.objects.filter(diff_months=True)