#16023 closed Bug (invalid)
Range query on a datetime is NOT inclusive with dates
Reported by: | 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)
follow-up: 2 comment:1 by , 13 years ago
Component: | Database layer (models, ORM) → Documentation |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:2 by , 13 years ago
Replying to aaugustin:
In Python and many other languages,
i in range(n)
means0 <= 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:4 by , 13 years ago
Not closed yet! Please update the document else. It'd be better if this has been "inclusive".
comment:5 by , 12 years ago
Version: | 1.3 → 1.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 , 12 years ago
Owner: | changed from | to
---|
comment:7 by , 12 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
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.
comment:9 by , 4 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))
In Python and many other languages,
i in range(n)
means0 <= i < n
. If I want all transactions from April, I'd use:In my opinion, Django's behavior is consistent, but the doc is misleading and must be improved.