I have a query that works in Django 1.9.9 but not 1.10. Running on CentOS
7, with python 2.7.5, and MySQL 5.6.23 on Amazon RDS
Example:
class TaskLog(models.Model):
id = models.AutoField(primary_key=True)
start = models.DateTimeField(auto_now_add=True)
stop = models.DateTimeField(blank=True, null=True, default=None)
Query = TaskLog.objects.extra(select = {"day": "left(start,
10)"}).values("day").annotate(sum=Sum(Func(F('stop'),
function='UNIX_TIMESTAMP') - Func(F('start'), function='UNIX_TIMESTAMP')))
In 1.9.9 the value for sum would be the sum of the number of seconds
between start and stop.
In 1.10 it is always None.
The sql generated is:
Django 1.9.9:
SELECT (left(start, 10)) AS `day`, SUM((UNIX_TIMESTAMP(`TaskLog`.`stop`) -
UNIX_TIMESTAMP(`TaskLog`.`start`))) AS `sum` FROM `TaskLog` WHERE
(`TaskLog`.`user_id` = 22) GROUP BY UNIX_TIMESTAMP(`TaskLog`.`stop`),
UNIX_TIMESTAMP(`TaskLog`.`start`), (left(start, 10)) ORDER BY NULL
Django 1.10:
SELECT (left(start, 10)) AS `day`, SUM(TIMESTAMPDIFF(MICROSECOND,
UNIX_TIMESTAMP(`TaskLog`.`start`), UNIX_TIMESTAMP(`TaskLog`.`stop`))) AS
`sum` FROM `TaskLog` WHERE (`TaskLog`.`user_id` = 22) GROUP BY (left(start,
10)) ORDER BY NULL
It looks like the new temporal subtraction feature to swap out the - with
TIMESTAMPDIFF is the problem. Maybe it shouldn't do that for
UNIX_TIMESTAMPs?
I fixed my app by simplifying my code to not convert to UNIX_TIMESTAMP
before subtracting:
Query = TaskLog.objects.extra(select = {"day": "left(start,
10)"}).values("day").annotate(sum=Sum(F('stop') - F('start')))
But thought I should still report that subtracting UNIX_TIMESTAMPs appears
to be broken
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/8309dec9-edfd-4b07-b9ed-26a19dec01b1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
