Django query with relative time using database's clock-CodePudding

I'm trying to find all the ORM objects updated in the last 30 seconds. The standard way to do this would be:

    reftime = timezone.now() - relativedelta(seconds=30)
    queryset = MyModel.objects.filter(updated_at__gte=reftime)

This works so long as all the application servers running the django app have their clocks synchronized, which sounds easy but in practice really isn't. Clock skew is real.

Fortunately the database server has a single clock which we should be able to rely on as the definitive clock for this kind of thing. And django has this nice object called Now() in django.db.models.functions which seems like it should be helpful for this kind of thing. But I've never seen any code sample for this method that does any math to shift the time by something like 30 seconds. e.g. this does NOT WORK:

    reftime = django.db.models.functions.Now() - relativedelta(seconds=30)

How can I do a django ORM query using a timestamp relative to the SQL server's clock?

CodePudding user response：

You should not use a relativedelta, but just a timedelta ^[Python-doc], so:

from datetime import timedelta
from django.db.models.functions import Now

queryset = MyModel.objects.filter(updated_at__gte=Now()-timedelta(seconds=30))

Django has implemented logic to subtract a timedelta from the Now class, and thus create an SQL expression for that. This is not the case for a relativedelta.

Indeed, we can show the queries that Django makes with:

>>> print(Event.objects.filter(updated_at__gte=Now()-relativedelta(seconds=30)).query)
SELECT `app_name_mymodel`.`id`, `app_name_mymodel`.`updated_at` FROM `app_name_mymodel` WHERE `app_name_mymodel`.`updated_at` >= (CURRENT_TIMESTAMP - relativedelta(seconds= 30))
>>> print(Event.objects.filter(updated_at__gte=Now()-timedelta(seconds=30)).query)
SELECT `app_name_mymodel`.`id`, `app_name_mymodel`.`updated_at` FROM `app_name_mymodel` WHERE `app_name_mymodel`.`updated_at` >= (CURRENT_TIMESTAMP - INTERVAL 30000000 MICROSECOND)

For a relativedelta it just uses the str(…) of the object, whereas for a timedelta object, it "understands" this type, and converts it to the corresponding SQL dialect equivalent.