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
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.