Opened 4 years ago

Closed 4 years ago

#19360 closed Bug (fixed)

annotate the sum of related TimeField

Reported by: Luc Saffre Owned by: chrismedrela
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: chrismedrela Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


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.

Attachments (2) (1.6 KB) - added by Luc Saffre 4 years ago.
2 files and
19360_v1.diff (2.3 KB) - added by chrismedrela 4 years ago.

Download all attachments as: .zip

Change History (10)

Changed 4 years ago by Luc Saffre

Attachment: added

2 files and

comment:1 Changed 4 years ago by chrismedrela

Cc: chrismedrela added
Component: UncategorizedDatabase layer (models, ORM)
Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug
Version: 1.4master

comment:2 Changed 4 years ago by chrismedrela

Owner: changed from nobody to 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.

comment:3 Changed 4 years ago by 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


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?

Changed 4 years ago by chrismedrela

Attachment: 19360_v1.diff added

comment:4 Changed 4 years ago by chrismedrela

Has patch: set

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

comment:5 Changed 4 years ago by Claude Paroz

Needs tests: set

Yes, tests are required. Thanks for the patch.

comment:6 Changed 4 years ago by Nick Sandford

Needs tests: unset

Added tests and documentation. New pull request here:

comment:7 Changed 4 years ago by Claude Paroz

Triage Stage: AcceptedReady for checkin

comment:8 Changed 4 years ago by Claude Paroz <claude@…>

Resolution: fixed
Status: newclosed

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

Note: See TracTickets for help on using tickets.
Back to Top