How to create a Django queryset filter comparing two date fields in the same model

django django-queryset

Trying to get a query where the Activity record is stale in my Solr Index. I want to check to see if the Activity.updated date in the database is greater than the Activity.added_toSolr_date for the same record.

stale_activities_queryset = Activity.objects.filter(updated__gte = self.added_toSolr_date)

Model

class Activity(models.Model):
    # Last time entry / metric was updated in the Activity model database
    updated =  models.DateTimeField( verbose_name="CRUD date")
    # When it was added to Solr Index Date
    added_toSolr_date = models.DateTimeField(blank=True, null=True, verbose_name="Added to Solr Index Date")

I referenced Django Query docs: https://docs.djangoproject.com/en/1.4/ref/models/querysets/ And unit tests for samples: https://github.com/django/django/blob/master/tests/modeltests/or_lookups/tests.py

Also searched here on Stackoverflow. All the examples use an entered date instead of comparing two date fields in the same model.

Community

F objects.

from django.db.models import F
stale_activities = Activity.objects.filter(updated__gte=F('added_toSolr_date'))

@CarlosFerreira, through years of using django every day. I can't really say when.

nice solution! nearly posted the same question

Thanks! I was looking for this!

This seems to fail if one of the dates is NULL. Using Q to check for null or compare and still fails. Even though it would not in MySQL, anyone else seeing this?

@PaulKenjora would another option be to first query and filter out anything with null dates, then filter again for the date comparison? Or do you need to include null dates in your results?

mlissner

The solution of Yuji Tomita, unfortunately, didn't work.

Consider a model below:

class list(models.Model):
    text = models.CharField(max_length=140)
    created = models.DateTimeField()
    modified = models.DateTimeField()

Queryset:

my_list = todolist.objects.order_by('created').filter(created__gte=F('modified'))

Template:

{% if my_list %}
{% for list in my_list %}
{{ list.text}}
{% endfor %}
{% else %}
<p>there is no list</p>
{% endif %}

In the end I get an empty list, but I know, it is not correct. I have also used the following inside the template (without queryset filter):

{% if list.created|date:'d m y h:i:s' == list.modified|date:'d m y h:i:s' %}

It worked, but I would prefer to see a more elegant solution.

At a quick read it looks like you are doing two different things in your queryset filter and y our template code. In the former you are using 'gte' and in the latter you are using '=='. Is this why the two don't produce the same results? I'm trying to figure out why using mydate__exact = F('my_other_date') in a query doesn't work when 'my_other_date' is None, myself.

I had another reason why it wouldn't work. Using django-extensions TimeStampedModel, the created and updated timestamps equal down to the milisecond. There is a minimal delay. My quick fix was to just compare it down to the second.

How to create a Django queryset filter comparing two date fields in the same model

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Links

Contact US