CODEWITHSUNDEEP
pythondjangodjango-orm
Cerin
Cerin

Reputation: 64729

Query Expired Time Difference in Django ORM

I have a simple model to describe a task and a period between executions of the task:

class Task(models.Model):
    last_check_timestamp = models.DateTimeField(blank=True, null=True, db_index=True)

class TaskSchedule(models.Model):
    task = models.OneToOneField(Task, blank=False, null=False, related_name='schedule')
    interval_seconds = models.IntegerField(blank=False, null=False)

I want to be able to query, using Django's ORM, which tasks are fresh and which are stale, meaning those that are due to be run according to their schedule.

I'm currently calculating this for a specific record without the ORM via:

import datetime
task = Task.objects.get(id=123)
fresh = task.last_check_timestamp is not None and datetime.datetime.now() < (task.last_check_timestamp + datetime.timedelta(seconds=task.schedule.interval_seconds))

How would I do the equivalent with Django's ORM, so I could query all fresh/stale tasks at once?

e.g. This obviously doesn't work, but something like:

Tasks.objects.filter(Q(last_check_timestamp__isnull=True) || Q(last_check_timestamp + datetime.timedelta(seconds=schedule__interval_seconds)))

Upvotes: 2

Views: 1729

Answers (1)

Alasdair
Alasdair

Reputation: 308829

I had to do a similar query recently. I was hoping to do it using F() objects, but I don't think this is possible, because the timedelta relies on a db column (it would be possible if schedule_interval_seconds was a constant).

In the end, I did the query using the ORM with extra(), and wrote SQL for the where clause.

sql = "DATE_ADD(last_check_timestamp, INTERVAL schedule.interval_seconds SECOND) < DATE(NOW())"
Tasks.objects.filter(Q(last_check_timestamp__isnull=True) || Q(last_check_timestamp + datetime.timedelta(seconds=schedule__interval_seconds)))
tasks = Task.object.select_related('schedule').extra(where=[sql,]

This uses the DATE_ADD function (MySQL docs). You may need to adjust for different flavours of SQL. The select_related is required so that Django does the join to the schedule table.

The above does not select the tasks where last_check_timestamp is null. I don't think you can use combine the extra() and your Q object, but you can expand the SQL statement.

sql = """last_check_timestamp is NULL or DATE_ADD(last_check_timestamp, INTERVAL schedule.interval_seconds SECOND) < DATE(NOW())"""

Upvotes: 3

Related Questions