CODEWITHSUNDEEP
django
GnarlyYoyo
GnarlyYoyo

Reputation: 31

Query annotation with date difference

I would like to annotate a query with the following expression: ( [Due Date] - [Now] ) / [Interval]

[Due Date] and [Interval] are fields from the database, while [Now] should be "equal" to timezone.now().

So this would look like:

.annotate(ratio=(F('due_date')-timezone.now())/F('Interval'))

but this does not work as timezone.now() is simply converted to its str representation. What I need is to convert it to a date representation, in a way which is not database dependent. Is this feasible with Django ORM? Then I would hope that the minus and division operators will be understood with the date format.

Upvotes: 3

Views: 1310

Answers (1)

mrts
mrts

Reputation: 18925

Use Julian dates and the database function Now instead of timezone.now():

from django.db.models import F, Func, IntegerField
from django.db.models.functions import Now

class JulianDay(Func):
    function = ''
    output_field = IntegerField()

    def as_postgresql(self, compiler, connection):
        self.template = "CAST (to_char(%(expressions)s, 'J') AS INTEGER)"
        return self.as_sql(compiler, connection)

    def as_sqlite(self, compiler, connection):
        self.template = 'julianday(%(expressions)s)'
        return self.as_sql(compiler, connection)


[...].annotate(ratio=(JulianDay(F('due_date'))-JulianDay(Now()))/F('Interval'))

If you need to use other database backends besides PostgreSQL and SQLite, see the Datediff function created by Michael Brooks.

Upvotes: 1

Related Questions