Opened 13 years ago

Closed 12 years ago

Last modified 3 years ago

#16023 closed Bug (invalid)

Range query on a datetime is NOT inclusive with dates

Reported by: jodym@… Owned by: Aymeric Augustin
Component: Documentation Version: 1.4
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (9)

comment:1 by Aymeric Augustin, 13 years ago

Component: Database layer (models, ORM)Documentation
Triage Stage: UnreviewedAccepted

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.

in reply to:  1 comment:2 by Jody McIntyre <jodym@…>, 13 years ago

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().

comment:3 by Aymeric Augustin, 12 years ago

UI/UX: unset

Change UI/UX from NULL to False.

comment:4 by bubalanisaipriyan@…, 12 years ago

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

comment:5 by stu.axon@…, 12 years ago

Version: 1.31.4

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).

comment:6 by Aymeric Augustin, 12 years ago

Owner: changed from nobody to Aymeric Augustin

comment:7 by Aymeric Augustin, 12 years ago

Resolution: invalid
Status: newclosed

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.

Last edited 12 years ago by Aymeric Augustin (previous) (diff)

comment:8 by Aymeric Augustin <aymeric.augustin@…>, 12 years ago

In [e69348b4e7f07ef927edaecc7126901fc91c79d0]:

Avoided mixing dates and datetimes in the examples.

Refs #16023.

comment:9 by suhailvs, 3 years ago

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))
Note: See TracTickets for help on using tickets.
Back to Top