[Django] #27856: Date subtraction loses accuracy on PostgreSQL for differences larger than month
Django
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.
Django
month
-------------------------------------+-------------------------------------
| Banaitis
Type: Bug | Status: assigned
Component: Database layer | Version: 1.10
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => assigned
* owner: nobody => Vytis Banaitis
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/8079 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/27856#comment:1>
Django
month
-------------------------------------+-------------------------------------
| Banaitis
Type: Bug | Status: assigned
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* version: 1.10 => master
* stage: Unreviewed => Accepted
Comment:
This is something I missed when implementing generalized temporal
subtraction in #24793, thanks for the report and patch!
--
Ticket URL: <https://code.djangoproject.com/ticket/27856#comment:2>
Django
month
-------------------------------------+-------------------------------------
| Banaitis
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"4045fd56cb0f83d0f78de9aca073c7104e4cf8fd" 4045fd56]:
{{{
#!CommitTicketReference repository=""
revision="4045fd56cb0f83d0f78de9aca073c7104e4cf8fd"
Fixed #27856 -- Improved accuracy of date subtraction on PostgreSQL.
Accuracy was incorrect when dates differ by a month or more.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27856#comment:3>
Django
month
-------------------------------------+-------------------------------------
| Banaitis
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"d03153e3b09c078a0622e45ecff37e43f3ab8972" d03153e3]:
{{{
#!CommitTicketReference repository=""
revision="d03153e3b09c078a0622e45ecff37e43f3ab8972"
[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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27856#comment:4>
Django
month
-------------------------------------+-------------------------------------
| Banaitis
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"92ce31fd8ced13384e3f5aea80a7d7f437870e74" 92ce31fd]:
{{{
#!CommitTicketReference repository=""
revision="92ce31fd8ced13384e3f5aea80a7d7f437870e74"
[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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27856#comment:5>