I recently worked on making substraction of temporal fields return a
`DurationField` on all database backends[1]:
class TemporalModel(models.Model):
from_date = models.DateField()
to_date = models.DateField()
from_time = models.TimeField()
to_time = models.TimeField()
from_datetime = models.DateTimeField()
to_datetime = models.DateTimeField()
TemporalModel.objects.annotate(
date_delta=F('to_date') - F('from_date'), # Will be a timedelta
time_delta=F('to_time') - F('from_time'), # Will be a timedelta
datetime_delta=F('to_datetime') - F('from_datetime'), # Will be a timedelta
)
Before this change the substraction operator either didn't work (MySQL, SQLite)
or returned inconsistent results (`DateField` - `DateField` returned an `int`
for the number of days on Oracle and PostgreSQL).
Now that all the tests are passing on all backends I'd like to get feedback on
two design decisions I took.
First, should we make substraction of `DateField`s return an integer instead of
a `timedelta` just like Oracle and PostgreSQL do by default? I initialy chose to
return a `timedelta` (use `DurationField` as `output_field`) because while it
made more sense to return an `int` from a SQL point of view I felt like the
former was more appropriate for the level of abstraction the ORM deals with. The
use of `timedelta` also introduces a backward incomptiblity issue for users of
Oracle and PostgreSQL before the unification effort.
My second concerns is about subtraction of temporals of different types. As
noted on the pull request I can see value in implementing the `DateField` and
`DateTimeField` cases but I'm not convinced it's even worth it.
For reference here's the different signatures of the `-` operator of PostgreSQL:
| LHS type | RHS type | Return type
| --------- | --------- | -------------
| date | date | integer
| date | time | timestamp
| date | timestamp | interval
| time | date | _undefined_
| time | time | interval
| time | timestamp | _undefined_
| timestamp | date | interval
| timestamp | time | timestamp
| timestamp | timestamp | interval
As usual, thanks for your feedback.
Simon
[1]
https://github.com/django/django/pull/5997