can we modify value in django queryset using Django "F" expression . Query optimisation

Question

now = datetime.utcnow().replace(tzinfo=utc)
    
.annotate(
        age=F(int((now - 'ended_at').total_seconds() / (60 * 60)))

I want to add logic like above in Django query. basically i want to calculate "age" that's a reason need to perform operation ORM. having large data and its taking time if I do this operation using for loop.

Answer 1

First define a Func to extract the number of seconds since the UNIX epoch.

from django.db.models import Func, IntegerField

class UnixTime (Func):
    """
    Extract the number of seconds since January 1, 1970.
    """

    arity = 1
    output_field = IntegerField()

    # for PostgreSQL
    def as_sql(self, compiler, connection, **extra_context):
        return super().as_sql(
            compiler, connection,
            template="EXTRACT(EPOCH FROM %(expressions)s)",
            **extra_context)

    def as_mysql(self, compiler, connection, **extra_context):
        return super().as_sql(
            compiler, connection,
            template="UNIX_TIMESTAMP(%(expressions)s)",
            **extra_context)

    def as_sqlite(self, compiler, connection, **extra_context):
        return super().as_sql(
            compiler, connection,
            template="CAST(strftime('%%%%s', %(expressions)s) AS INTEGER)",
            **extra_context)

Then make a query like this:

from django.db.models import F
from django.db.models.functions import Now

YourObject.objects.annotate(
    age=(UnixTime(Now()) - UnixTime(F('ended_at'))) / 3600
)