#27856: Date subtraction loses accuracy on PostgreSQL for differences larger
than
month
-------------------------------------+-------------------------------------
Reporter: Vytis | Owner: nobody
Banaitis |
Type: Bug | Status: new
Component: Database | Version: 1.10
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27856>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/049.64c3cb030846a7722dc55532af7aef82%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
