Request works with sqlite not with Postgres

[Tazo Gil]

Hello,

The following request works in my dev django project (sqlite) by not in production with postgres

out: >>> Questionnaire.objects.filter(site_id=1).annotate(moyenne=Avg('chrono__temps')).values('title','moyenne')

The errors :

 out: Traceback (most recent call last):
 out:   File "<console>", line 1, in <module>
 out:   File "/home/mimi/.virtualenvs/atmav2/local/lib/python2.7/site-packages/django/db/models/query.py", line 234, in __repr__
 out:     data = list(self[:REPR_OUTPUT_SIZE + 1])
 out:   File "/home/mimi/.virtualenvs/atmav2/local/lib/python2.7/site-packages/django/db/models/query.py", line 258, in __iter__
 out:     self._fetch_all()
 out:   File "/home/mimi/.virtualenvs/atmav2/local/lib/python2.7/site-packages/django/db/models/query.py", line 1074, in _fetch_all
 out:     self._result_cache = list(self.iterator())
 out:   File "/home/mimi/.virtualenvs/atmav2/local/lib/python2.7/site-packages/django/db/models/query.py", line 112, in __iter__
 out:     for row in compiler.results_iter():
 out:   File "/home/mimi/.virtualenvs/atmav2/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 808, in results_iter
 out:     row = self.apply_converters(row, converters)
 out:   File "/home/mimi/.virtualenvs/atmav2/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 792, in apply_converters
 out:     value = converter(value, expression, self.connection, self.query.context)
 out:   File "/home/mimi/.virtualenvs/atmav2/local/lib/python2.7/site-packages/django/db/models/expressions.py", line 283, in convert_value
 out:     return float(value)
 out: TypeError: float() argument must be a string or a number

Thank you for your help.

[Tazo Gil]

I forgot to precise the type of the  field Chrono.temps:
temps = models.DurationField(null=True)

[Simon Charette]

Hi Tazo,

The default `output_field` of the `Avg` aggregate is a `FloatField`[1] hence
the error raised here. In the case of SQLite, `DurationField`s are stored as
large integers of microseconds[2] and the result returned by the database is
already a float making `float(value)` a no-op in `convert_value()`[3].

Since Django 1.9 it's possible to specify the `output_field` of an `Avg`
expression as documented[4].

In your case you want to do the following:

Questionnaire.objects.filter(
    site_id=1
).annotate(
   moyenne=Avg('chrono__temps', output_field=DurationField())
).values('title','moyenne')

Au plaisir,
Simon

[1] https://github.com/django/django/blob/fdf5cd3429369954e8deb764d9f30f6374581613/django/db/models/aggregates.py#L45
[2] https://docs.djangoproject.com/en/1.9/ref/models/fields/#durationfield
[3] https://github.com/django/django/blob/master/django/db/models/expressions.py#L282-L283
[4] https://docs.djangoproject.com/en/1.9/ref/models/querysets/#django.db.models.Avg

[Tazo Gil]

Without your help, I would not get the solution. Maybe the doc (https://docs.djangoproject.com/en/1.9/ref/models/querysets/#avg) should be more precise about this case.
Merci beaucoup

[Simon Charette]

Hi Tazo,

I just submitted a patch that correctly resolves the `output_field` of `Avg`
over non-numeric fields.

If it gets merged you shouldn't have to worry about passing an explicit
`output_field=DurationField()` in Django 1.10.

Simon