Why django.db.models.Avg () returns a datetime object? And why is that not the case?


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.

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.