Opened 8 years ago

Closed 8 years ago

Last modified 8 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: dev
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 by Vytis Banaitis, 8 years ago

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

comment:2 by Simon Charette, 8 years ago

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 by Tim Graham <timograham@…>, 8 years ago

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 by Tim Graham <timograham@…>, 8 years ago

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 by Tim Graham <timograham@…>, 8 years ago

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