Why doesn't django.db.models.Avg() return a datetime object? And why is what it returns wrong?

Question

Here is my Django model:

class MyModel(models.Model):
    a = IntegerField()
    b = DateTimeField()

Here is the QuerySet I execute on this model to find the count, min, max and average bs for each value of a:

>>> MyModel.objects.values('a').annotate(count=Count("b"), min=Min('b'), max=Max('b'), avg=Avg('b'))

{'a': 1, 'count': 2, 
 'avg': 20150226046183.0, 
 'min': datetime.datetime(2015, 2, 26, 1, 8, 22, tzinfo=<UTC>), 
 'max': datetime.datetime(2015, 2, 26, 8, 15, 44, tzinfo=<UTC>)}

Look at the value associated with the avg key. It is a float. Why? The average of DateTimeFields should be a DateTimeField, right? So why is it a float of all things? I could even understand a string - but definitely not a float. This is not decimal numeric data.

Once we get past that problem, I can parse out the data from the float to figure out what Django means. I can see 20150225 obviously means February 26, 2015. But wait, that means the time of day would be represented by the 046183.0. What the heck does that even mean? 4:61 am and 83 seconds?? It makes no sense.

Can someone please explain all this to me? It seems a bit of a mess.

What is the average of March 2nd 2014, September 3rd 1886 and December 12th 1949? — , Feb 26 '15 at 10:08
@LegoStormtroopr I was thinking this way: every datetime can be represented as a point on a half-open line segment with a defined endpoint (therefore it can be represented as a numeric value e.g. as time elapsed from the endpoint, so arithmetic mean is computable). — inejc, Feb 26 '15 at 15:47

score 2 · Answer 1 · edited May 23 '17 at 11:56

From the source code Avg always returns a float:

class Avg(Aggregate):
    function = 'AVG'
    name = 'Avg'

    def __init__(self, expression, **extra):
        super(Avg, self).__init__(expression, output_field=FloatField(), **extra)

    def convert_value(self, value, expression, connection, context):
        if value is None:
            return value
        return float(value)

What you could do is transform your datetime object to unix timestamp (number of seconds elapsed since the beginning of the year 1970 without leap seconds) and calculate average using the extra() method.

MyModel.objects.extra('avg': 'AVG(UNIX_TIMESTAMP(b))'.values('avg')

I suggest you read this before converting between unix time and datetime objects since you have to take into account the difference between naive and aware datetimes.

Your answer is helpful and gets me close. But it poses other problems for me. There are also some syntax errors in the answer you posted. So I asked a similar question here: http://stackoverflow.com/questions/28758714/how-to-write-a-django-queryset-the-properly-computes-average-of-datetimefield-wi — Saqib Ali, Feb 27 '15 at 06:17

score 1 · Answer 2 · answered Feb 26 '15 at 10:03

From django's source code, it always return float. It looks like the intention of Avg is to be used with numbers.

You can try to convert the returned float using strptime, I haven't tried this nor am I sure if the float number returned is the average value you are looking for.

Why doesn't django.db.models.Avg() return a datetime object? And why is what it returns wrong?

2 Answers2