annotate the sum of related TimeField

[Luc Saffre]

I get a "TypeError: expected string or buffer" when I try to annotate the sum of a timefield in a related model.

class Ticket(models.Model):  
    name = models.CharField(max_length=200)

class Session(models.Model):  
    ticket = models.ForeignKey(Ticket,related_name="sessions")
    time = models.TimeField()

qs = Ticket.objects.annotate(timesum=models.Sum('sessions__time'))
print [unicode(t.timesum) for t in qs]

Please see the attached files for a complete test case.

[Luc Saffre]

2 files models.py and settings.py

[chrismedrela]

The problem is that django.utils.dateparse.parse_time function is called with float argument instead of string. Then the argument is passed on to re_pattern.match function.

[chrismedrela]

The problem is that there is no native time field in sqlite. This feature is emulated by Django -- the time is saved as text (HH:MM:SS). The query

Ticket.objects.annotate(timesum=models.Sum('sessions__time'))

is translated into the following SQL statement:

SELECT ..., SUM("polls_session"."time") AS "timesum" FROM "polls_ticket" LEFT OUTER JOIN ...

The sum expression doesn't make sense since "polls_session"."time" is a text column, but it fails silently -- the sum returned by sqlite3 driver is 0.0 float. Then Django try to parse the result as a time using re module, but the result it's not even string which causes raising the exception.

What can we do? We can use integer column representing unix time to emulate date/time fields, but it's lot of work and it breaks backward compatibility. We should at least add note to documentation. Django should fail loudly when you try to aggregate date/time fields in sqlite. Any other ideas?

[chrismedrela]

I've written a patch (and created pull request -- see ​https://github.com/django/django/pull/579). It raises an explicit exception if you aggregate on date/time field in sqlite3. Are tests required for fixes like that?

[Claude Paroz]

Yes, tests are required. Thanks for the patch.

[Nick Sandford]

Added tests and documentation. New pull request here: ​https://github.com/django/django/pull/637.

[Claude Paroz <claude@…>]

In eb6c107624930c97390185fdbf7f887c50665808:

Fixed #19360 -- Raised an explicit exception for aggregates on date/time fields in sqlite3

Thanks lsaffre for the report and Chris Medrela for the initial
patch.