How to annotate Django query with days calculation?

Question

How do you annotate a Django query of a table with the calculation of days since the record was created?

I want to order a table based on a combined metric of priority multiplied by days since the record was created.

To get the days annotation, I'm trying to do:

qs = qs.annotate(days=(ExtractDay(Now()-F('created_datetime'))))

However, this is throwing the error:

django.db.utils.ProgrammingError: function pg_catalog.timezone(unknown, interval) does not exist
LINE 1: ...EMENT_TIMESTAMP() - "myapp_mymodel"."created_timestamp") AT TIME ZO...
                                                         ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

I've tried the similar Extract() function, but it also produces a similar error.

What am I doing wrong?

Ostensibly, I just need to do the Django equivalent of EXTRACT(DAY FROM NOW() - created_timestamp), but Django's Extract(..., 'days') and ExtractDay() functions don't appear to actually match the PostgreSQL equivalents.

It looks like both the value returned by Django's Now() and my timestamp field are timezone-aware timestamps, so why is the database complaining about incompatible types?

My database backend is PostgreSQL.

Abdul Aziz Barkat · Accepted Answer · 2021-05-21 17:12:45Z

6

Your query needs to specify what type the output of Now() - F('created_datetime') will be, since both are datetimes then Django assumes the output to also be a datetime (False here but works normally in other cases), hence you should use ExpressionWrapper [Django docs] and provide an output_field:

from django.db.models import DurationField, ExpressionWrapper

qs = qs.annotate(days=(ExtractDay(ExpressionWrapper(Now() - F('created_datetime'), output_field=DurationField()))))

answered May 21, 2021 at 17:12

Abdul Aziz Barkat

22.3k9 gold badges106 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to annotate Django query with days calculation?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related