Opened 5 years ago

Closed 5 years ago

Last modified 14 months ago

#24793 closed New feature (fixed)

Subtracting DateTime fields in a query expression should use timediff

Reported by: Ben Buchwald Owned by: Simon Charette
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: datetime duration expression
Cc: josh.smeaton@…, Simon Charette Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

If I have a model with start and end DateTime fields, I'd like to be able to annotate a query with the duration. For instance, with the model

class Event(Model):
    start_time = DateTimeField()
    end_time = DateTimeField()

I want to be able to do the following query: Event.objects.annotate(duration=ExpressionWrapper(F('end_time')-F('start_time'),output_field=DurationField())). However this yields and incorrect result. At least in MySQL, the proper SQL is timediff(end_time,start_time) as duration. I have not found an alternate way to do this with Django's ORM without writing SQL.

Change History (11)

comment:1 Changed 5 years ago by Josh Smeaton

Cc: josh.smeaton@… added
Triage Stage: UnreviewedAccepted
Type: UncategorizedNew feature

Backends that don't have native duration types (mysql and sqlite) do not have support for date - date annotations. This is guarded by the has_native_duration_field flag in tests.

You can implement your own expression that uses timediff though:

class TimeDiff(Func):
    function = 'timediff'
    output_field = TimeField()
    # you could also implement __init__ to enforce only two date fields

Event.objects.annotate(duration=TimeDiff('end_time', 'start_time')

Note that I've made the output_field a TimeField rather than a DurationField. MySQL returns Time as the result of date-date, not a duration/interval.

Considering that adding/subtracting dates is relatively common, I wouldn't mind seeing better support for mysql and sqlite if it's possible. So, I'm accepting on that basis. If someone is able to provide a patch that makes F('date') - F('otherdate') work consistently on all backends, then we'd probably accept that.

comment:2 Changed 5 years ago by Samuel Spencer

@jarshwah

I've got a work around that I think works for SQLite using SQLites native julianday function, and am keen to progress this as an actual solution, but the code is a little dense for me.

Would you be able to point me in the direction of where the subtract expressions are handled?

comment:3 Changed 5 years ago by Josh Smeaton

Hi Lego,

I'm really sorry I've taken so long to get back to you, I'll do better in future.

Where I think you should be looking is django.db.models.expression.CombinedExpression.as_sql() method.

You would put some handling in there to check left and right hand side for two dates, and then we'd probably have some backend specific SQL that knows how to handle date(+-)date.

If you look in the as_sql method you'll see how DurationExpressions are handled. It's guarded by a feature flag (has_native_duration_field). You might want to guard by another feature flag, something like "supports_date_arithmetic". You could then create a DateExpression class that handles the backend specific way of doing date math.

Hopefully that's enough information for you to get started. Please hit me up if you need some help or clarification.

comment:4 Changed 5 years ago by Simon Charette

Cc: Simon Charette added

I tried to help someone on the user mailing list with a similar issue with time fields.

I came up with tests that shows that all but DateField combinations work on PostgreSQL while all combinations fails on SQLite3:

from django.db import models


class DateModel(models.Model):
    start = models.DateField()
    end = models.DateField()


class TimeModel(models.Model):
    start = models.TimeField()
    end = models.TimeField()


class DateTimeModel(models.Model):
    start = models.DateTimeField()
    end = models.DateTimeField()
import datetime

from django.db.models import DurationField, ExpressionWrapper, F
from django.test import TestCase
from pytz import UTC

from . import models


class DurationTests(TestCase):
    @classmethod
    def setUpTestData(cls):
        models.DateModel.objects.create(
            start=datetime.date(2015, 5, 10),
            end=datetime.date(2015, 5, 12),
        )
        models.TimeModel.objects.create(
            start=datetime.time(10),
            end=datetime.time(13, 30),
        )
        models.DateTimeModel.objects.create(
            start=datetime.datetime(2015, 5, 10, 10, 00, tzinfo=UTC),
            end=datetime.datetime(2015, 5, 12, 13, 30, tzinfo=UTC),
        )

    def test_timemodel(self):
        self.assertEqual(
            models.TimeModel.objects.annotate(duration=ExpressionWrapper(
                F('end') - F('start'), output_field=DurationField()
            )).get().duration, datetime.timedelta(hours=3, minutes=30)
        )

    def test_datemodel(self):
        self.assertEqual(
            models.DateModel.objects.annotate(duration=ExpressionWrapper(
                F('end') - F('start'), output_field=DurationField()
            )).get().duration, datetime.timedelta(days=2)
        )

    def test_datetimemodel(self):
        self.assertEqual(
            models.DateTimeModel.objects.annotate(duration=ExpressionWrapper(
                F('end') - F('start'), output_field=DurationField()
            )).get().duration, datetime.timedelta(days=2, hours=3, minutes=30)
        )

PostgreSQL failure:

======================================================================
FAIL: test_datemodel (duration_expression.tests.DurationTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/simon/workspace/ticketing/duration_expression/tests.py", line 40, in test_datemodel
    )).get().duration, timedelta(days=2)
AssertionError: 2 != datetime.timedelta(2)

----------------------------------------------------------------------

SQLite3 failures:

======================================================================
FAIL: test_datemodel (duration_expression.tests.DurationTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/simon/workspace/ticketing/duration_expression/tests.py", line 37, in test_datemodel
    )).get().duration, datetime.timedelta(days=2)
AssertionError: datetime.timedelta(0) != datetime.timedelta(2)

======================================================================
FAIL: test_datetimemodel (duration_expression.tests.DurationTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/simon/workspace/ticketing/duration_expression/tests.py", line 44, in test_datetimemodel
    )).get().duration, datetime.timedelta(days=2, hours=3, minutes=30)
AssertionError: datetime.timedelta(0) != datetime.timedelta(2, 12600)

======================================================================
FAIL: test_timemodel (duration_expression.tests.DurationTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/simon/workspace/ticketing/duration_expression/tests.py", line 30, in test_timemodel
    )).get().duration, datetime.timedelta(hours=3, minutes=30)
AssertionError: datetime.timedelta(0, 0, 3) != datetime.timedelta(0, 12600)

----------------------------------------------------------------------

comment:5 Changed 5 years ago by Simon Charette

Owner: changed from nobody to Simon Charette
Status: newassigned

comment:6 Changed 5 years ago by Simon Charette

Has patch: set

comment:7 Changed 5 years ago by Tim Graham

Needs documentation: set

Needs a mention in the release notes for third-party database backends.

comment:8 Changed 5 years ago by Simon Charette

Needs documentation: unset
Version: 1.8master

comment:9 Changed 5 years ago by Tim Graham

Triage Stage: AcceptedReady for checkin

comment:10 Changed 5 years ago by Simon Charette <charette.s@…>

Resolution: fixed
Status: assignedclosed

In 766afc22:

Fixed #24793 -- Unified temporal difference support.

comment:11 Changed 14 months ago by Mariusz Felisiak <felisiak.mariusz@…>

In 088a6fa:

Refs #24793 -- Removed bogus connection argument from SQLCompiler.compile() calls.

The method doesn't expect a connection object to be passed as its second
argument.

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