how to get average of two date fields

Question

I want to subtract the time user's result object is created and the time user has registered. these are my models:

class User(AbstractUser):
    id = models.AutoField(primary_key=True)
    created_at = models.DateTimeField(auto_now_add=True)
    username = models.CharField(unique=True, max_length=13)
    first_name = models.CharField(max_length=32, null=True, default=None)
    last_name = models.CharField(max_length=64, null=True, default=None)

class Results(models.Model):
    quiz = models.ForeignKey(Quiz, on_delete=models.CASCADE)
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    created = models.DateField(auto_now_add=True, blank=True, null=True)
    score = models.FloatField()

in my views, I want something like this: (Sum of all Results.created dates - Sum of all User.created_at dates)/ len(all users). how can I achieve this?

I userd the aggregate to write this line of code for testing:

Results.objects.filter(user__is_staff=False, user__role='applicant').aggregate(Avg(F('created')))

but it gives me this error:

You cannot use Sum, Avg, StdDev, and Variance aggregations on date/time fields in sqlite3 since date/time is saved as text.

You should use `aggregate()` method ([see docs](https://docs.djangoproject.com/en/4.0/topics/db/aggregation/)) — Christophe, Jan 26 '22 at 08:32
I edited my question. I used this aggregate but it gives me an error see the question — Sam, Jan 26 '22 at 08:45

score 3 · Accepted Answer · answered Jan 26 '22 at 10:04

I assume you're computing the average difference between user account creation date and the date each of their quizzes is completed. Imagine we have three Results:

User A completes the quiz 3 days after creating their account.
User A completes a second quiz 4 days after creating their account.
User B completes a quiz 5 days after creating their account.

The computation would be the average of 3, 4, and 5.

You can accomplish this with Django's query expressions.

For better accuracy, change your created field in Results to a DateTimeField instead of a DateField. Otherwise, your calculations won't account for partial days.
Use the F function to refer to your desired fields: F('created') refers to a Result's creation date, and F('user__created_at') refers to the Result's corresponding User's account creation date.
Use Func to create a JulianDay class, which represents SQLite's JulianDay function. This function lets you find the number of days between two dates.
Compute an Avg aggregation, using a FloatField() as the output field's data type. (Note: You can remove the output_field=FloatField() part once you change your created field in Results to a DateTimeField.)

# Import your models
from question70860461.models import User, Quiz, Results

# Import Django query expressions
from django.db.models import F, Func, FloatField, Avg

# Set up some example objects
u = User(username='jeffb', first_name='Jeff', last_name='Booth')
u.save()

q = Quiz()
q.save()

r = Results(quiz=q, user=u, score=100)
r.save()

r2 = Results(quiz=q, user=u, score=200)
r2.save()

# The usual trick to find the days between two dates on SQLite
# is to use the JulianDay function. Create a Django Func wrapper
# for this function:
class JulianDay(Func):
    function='JulianDay'

# Run a query that finds the average
# time between User creation and Results creation.
x = Results.objects.aggregate(
      result=Avg(JulianDay(F('created')) - JulianDay(F('user__created_at')),
             output_field=FloatField()))

I did all this but it still gives me this error: class JulianDay(Func): function='JulianDay' — Sam, Feb 03 '22 at 06:24
@Sam Could you please paste the full error message into your comment? This worked for me in my Python manage.py shell. — Jeff Booth, Feb 03 '22 at 07:08

how to get average of two date fields

1 Answers1