Date subtraction loses accuracy on PostgreSQL for differences larger than month

[Vytis Banaitis]

On PostgreSQL backend date subtraction is implemented via age function.

From ​PostgreSQL docs:

Subtract arguments, producing a "symbolic" result that uses years and months, rather than just days

When converting this year-month-day interval to a python timedelta, year equals 365 days and month equals 30 days. This can (and in the case of longer intervals, most likely will) differ from the result of the same subtraction done in python.

Example:

class DateModel(models.Model):
    d1 = models.DateField()
    d2 = models.DateField()

In [2]: DateModel.objects.create(d1=datetime.date(2017, 2, 5), d2=datetime.date(2016, 3, 1))

In [3]: dm = DateModel.objects.annotate(diff=F('d1') - F('d2')).get()

In [4]: dm.diff
Out[4]: datetime.timedelta(334)

In [5]: dm.d1 - dm.d2
Out[5]: datetime.timedelta(341)

Solution:

Use date subtraction which returns an integer, the difference in days, and convert it to an interval in days only:

(interval '1 day' * (lhs - rhs))

Or on PostgreSQL 9.4 or later:

make_interval(days := lhs - rhs)

[Vytis Banaitis]

​PR

[Simon Charette]

This is something I missed when implementing generalized temporal subtraction in #24793, thanks for the report and patch!

[Tim Graham <timograham@…>]

In 4045fd56:

Fixed #27856 -- Improved accuracy of date subtraction on PostgreSQL.

Accuracy was incorrect when dates differ by a month or more.

[Tim Graham <timograham@…>]

In d03153e3:

[1.11.x] Fixed #27856 -- Improved accuracy of date subtraction on PostgreSQL.

Accuracy was incorrect when dates differ by a month or more.

Backport of 4045fd56cb0f83d0f78de9aca073c7104e4cf8fd from master

[Tim Graham <timograham@…>]

In 92ce31fd:

[1.10.x] Fixed #27856 -- Improved accuracy of date subtraction on PostgreSQL.

Accuracy was incorrect when dates differ by a month or more.

Backport of 4045fd56cb0f83d0f78de9aca073c7104e4cf8fd from master