Subtracting UNIX_TIMESTAMPS broke in Django 1.10

[Dan Edwards]

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

[Simon Charette]

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')))