[Django]-Get record's age in seconds if older than 5 minutes (otherwise 0) in Django (with PostgreSQL database)

by

2👍

I figured it out:

Interfaces.objects.all()
.annotate(
   age=Case(
          When(timestamp__lt=datetime.utcnow() - timedelta(minutes=5),
               then=Cast(Epoch(datetime.utcnow() - F('timestamp')),
                         NullIntegerField)),
          default=0,
          output_field=NullIntegerField
   ),
)
.order_by('age','ip')

By the way, my imports and relevant settings:

from django.db.models import  F, Func, Case, When, IntegerField
from django.db.models.functions import Coalesce, Cast

NullIntegerField = IntegerField(null=True)

class Epoch(Func):
    function = 'EXTRACT'
    template = "%(function)s('epoch' from %(expressions)s)"

This website ended up being the most helpful: https://micropyramid.com/blog/django-conditional-expression-in-queries/

👤Granny Aching

0👍

You can do it in other way also which will be faster.
Get current time, subtract from that 5 minutes, after that search all the Interfaces
where age is less or equal than the subtracted date.

example:

current_time = datetime.now()
older_than_five = current_time - datetime.timedelta(minutes=5)

Interfaces.objects.all()
.annotate(
   age=Case(
       When(age__lt=older_than_five, then=Value(0)),
       default=F('age')
   )
)
.order_by('age','ip')
👤Jazzy

Leave a comment