Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#27856 closed Bug (fixed)

Date subtraction loses accuracy on PostgreSQL for differences larger than month

Reported by: Vytis Banaitis Owned by: Vytis Banaitis
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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)

Change History (5)

comment:1 Changed 3 years ago by Vytis Banaitis

Has patch: set
Owner: changed from nobody to Vytis Banaitis
Status: newassigned

comment:2 Changed 3 years ago by Simon Charette

Triage Stage: UnreviewedAccepted
Version: 1.10master

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

comment:3 Changed 3 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In 4045fd56:

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

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

comment:4 Changed 3 years ago by 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

comment:5 Changed 3 years ago by 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

Note: See TracTickets for help on using tickets.
Back to Top