Range query on a datetime is NOT inclusive with dates

[jodym@…]

I'm using a range test as part of a QuerySet on a DateTimeField. Based on the documentation, I expect this to be inclusive, but if I pass in a datetime.date it is not.

Here's a simple example:

# models:
class Transaction(models.Model):
    posted = models.DateTimeField()

# views:
    start_date = datetime.date(2011, 4, 1)
    end_date = datetime.date(2011, 4, 30)
    t = Transaction.objects.filter(posted__range=(start_date, end_date))
    # t does NOT contain transactions posted on 2011-04-30, despite range being "inclusive".

Printing t.query reveals:

SELECT "datebug_transaction"."id", "datebug_transaction"."posted" FROM "datebug_transaction" WHERE "datebug_transaction"."posted" BETWEEN 2011-04-01 00:00:00 and 2011-04-30 00:00:00

The conversion to a 00:00:00 datetime.datetime occurs at:

django/db/models/fields/__init__.py(682)to_python()
--> 682         if isinstance(value, datetime.date):
    683             return datetime.datetime(value.year, value.month, value.day)

The time part needs to be 00:00:00 for the start of the range and 23:59:59.999999 for the end.

[Aymeric Augustin]

In Python and many other languages, i in range(n) means 0 <= i < n. If I want all transactions from April, I'd use:

    start_date = datetime.date(2011, 4, 1)
    end_date = datetime.date(2011, 5, 1)

In my opinion, Django's behavior is consistent, but the doc is misleading and must be improved.

[Jody McIntyre <jodym@…>]

Replying to aaugustin:

In Python and many other languages, i in range(n) means 0 <= i < n. If I want all transactions from April, I'd use:

    start_date = datetime.date(2011, 4, 1)
    end_date = datetime.date(2011, 5, 1)

That will give all transactions between 2011-04-01 00:00:00 and 2011-05-01 00:00:00 inclusive, which isn't quite the same thing (it will include transactions dated exactly 2011-05-01 00:00:00.)

What's worse is that if start_date and end_date are DateField(), you'll get all transactions on 2011-05-01, which is inconsistent with the behaviour if it's a DateTimeField().

[Aymeric Augustin]

Change UI/UX from NULL to False.

[bubalanisaipriyan@…]

Not closed yet! Please update the document else. It'd be better if this has been "inclusive".

[stu.axon@…]

This is still the case with 1.4, documentation needs updating to remove 'inclusive'
​https://docs.djangoproject.com/en/dev/ref/models/querysets/

Please change:

range

Range test (inclusive).

To

range

Range test (not inclusive).

[Aymeric Augustin]

Scrap my first comment -- __range translates to a SQL BETWEEN, which is inclusive.

The real problem here is that you're mixing dates and datetimes. This is just asking for trouble. (It'll be even worse if you set USE_TZ to True.)

If you want to use range, the correct idiom is:

start_date = datetime.date(2011, 4, 1)
end_date = datetime.date(2011, 4, 30)
Transaction.objects.filter(posted__range=(
    datetime.datetime.combine(start_date, datetime.time.min),
    datetime.datetime.combine(end_date, datetime.time.max),
))

I'd rather write that as:

start_dt = datetime.datetime(2011, 4, 1)
end_dt = datetime.datetime(2011, 5, 1)
Transaction.objects.filter(posted__gte=start_dt, posted__lt=end_dt)

Dates and datetimes aren't interchangeable. I ​talked about this a few months ago.

[Aymeric Augustin <aymeric.augustin@…>]

In [e69348b4e7f07ef927edaecc7126901fc91c79d0]:

Avoided mixing dates and datetimes in the examples.

Refs #16023.

[suhail vs]

i would add 1 day to end_date, ie:

import datetime
Transaction.objects.filter(posted__gte=start_date, posted__lt=end_date+datetime.timedelta(days=1))