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 [email protected].
To post to this group, send email to [email protected].
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.
