Hi Dan!
I'm the author of the temporal substraction changes in 1.10.
I believe the main culprit here is your usage of `Func` with no explicit
`output_field`. When this parameter is not specified the return type of
`Func`
expressions is determined by it's wrapped expressions given there's either
only one or they are all of the same type[1].
In your case the `Func('start', function='UNIX_TIMESTAMP')` expression is
assumed to have a return type of `models.DateTimeField` as it's the data
type of
the `TaskLog.start` field. It should have been declared with an
`output_field`
of `models.IntegerField()` instead.
Let me know if that doesn't make sense to you.
Cheers,
Simon
[1]
https://github.com/django/django/blob/19e20a2a3f763388bba9263d56db34012e90cf5b/django/db/models/expressions.py#L265-L270
P.-S. You should replace your `.extra()` usage with an `annotate()` call as
this API is meant to be deprecated:
TaskLog.objects.annotate(
day=Func('start', 10, function='LEFT', output_field=models.CharField()),
).values('day').annotate(sum=Sum(F('stop') - F('start')))
Le mardi 16 août 2016 13:48:37 UTC-4, Dan Edwards a écrit :
>
> 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/7db6868b-6b0f-47e4-8eed-b1567c3b3854%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
