[SOLVED] How to do aggregate raw sql with julian date in sqlite (with django)

How to do aggregate raw sql with julian date in sqlite (with django)

I'm using sqlite3 and django. I wish to calculate the average for all the number of days between last_played and now. (Last_played is just a datetime field).

I have so far:

    avg_days_last_played_sql = """
        AVG(julianday('now') - julianday(played_at))
    """

    # Annotate the average days last played
    average_days_last_played = Song.objects.aggregate(
        avg_days_last_played=RawSQL(avg_days_last_played_sql, [])
    )['avg_days_last_played']

But it gives the error:

TypeError: avg_days_last_played is not an aggregate expression

Solution

I don't think you need a custom raw query for this. We can work with:

from django.db.models import FloatField
from django.db.models.expressions import Func


class JulianDay(Func):
    function = 'julianday'
    output_field = FloatField()

and then work with:

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

Song.objects.aggregate(
    avg_days_last_played=Avg(JulianDay(Now()) - JulianDay(F('played_at')))
)['avg_days_last_played']

We can thus now use the JulianDay function in all sorts of other expressions as well, thus not only solving this problem, but making related problems more convenient to solve as well.

That being said, storing the played_at in a song looks strange, typically you make a separate model that stores for each song when it was played, such that you can do additional filtering, aggregating, etc.