id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
27856	Date subtraction loses accuracy on PostgreSQL for differences larger than month	Vytis Banaitis	Vytis Banaitis	"On PostgreSQL backend date subtraction is implemented via `age` function.

From [https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TABLE 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:
{{{#!python
class DateModel(models.Model):
    d1 = models.DateField()
    d2 = models.DateField()
}}}
{{{#!python
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)
}}}"	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed			Accepted	1	0	0	0	0	0