sqldjangosqlite

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.