[Django] #16023: Range query on a datetime is NOT inclusive with dates
Django
---------------------+----------------------------------------------
Reporter: jodym@… | Owner: nobody
Type: Bug | Status: new
Milestone: | Component: Database layer (models, ORM)
Version: 1.3 | Severity: Normal
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Easy pickings: 0
---------------------+----------------------------------------------
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.
--
Ticket URL: <http://code.djangoproject.com/ticket/16023>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Type: Bug | Status: new
Version: 1.3 | Severity: Normal
Resolution: | Keywords:
Triage Stage: Accepted | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
------------------------------------+-------------------------------
Changes (by aaugustin):
* needs_docs: => 0
* needs_better_patch: => 0
* component: Database layer (models, ORM) => Documentation
* needs_tests: => 0
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/16023#comment:1>
Django
Type: Bug | Status: new
Version: 1.3 | Severity: Normal
Resolution: | Keywords:
Triage Stage: Accepted | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
------------------------------------+-------------------------------
Comment (by Jody McIntyre <jodym@…>):
Replying to [comment:1 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().
--
Ticket URL: <https://code.djangoproject.com/ticket/16023#comment:2>
Django
Type: Bug | Status: new
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------+------------------------------------
Comment (by bubalanisaipriyan@…):
Not closed yet! Please update the document else. It'd be better if this
has been "inclusive".
--
Ticket URL: <https://code.djangoproject.com/ticket/16023#comment:4>
Django
Type: Bug | Status: new
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+------------------------------------
* version: 1.3 => 1.4
Comment:
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).
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/16023#comment:5>
Django
Reporter: jodym@… | Owner: aaugustin
Component: Documentation | Version: 1.4
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by aaugustin):
* owner: nobody => aaugustin
--
Ticket URL: <https://code.djangoproject.com/ticket/16023#comment:6>
Django
Reporter: jodym@… | Owner: aaugustin
Component: Documentation | Version: 1.4
Severity: Normal | Resolution: invalid
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+-------------------------------------
Changes (by aaugustin):
* status: new => closed
* resolution: => invalid
Comment:
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.date(2011, 4, 1)
end_dt = datetime.date(2011, 5, 1)
Transaction.objects.filter(posted__gte=start_dt, posted__lt=end_dt)
}}}
Dates and datetimes aren't interchangeable. I [http://lanyrd.com/2012
/djangocon-europe/srptc/ talked about this] a few months ago.
--
Ticket URL: <https://code.djangoproject.com/ticket/16023#comment:7>
Django
Reporter: jodym@… | Owner: aaugustin
Type: Bug | Status: closed
Component: Documentation | Version: 1.4
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+-------------------------------------
Comment (by Aymeric Augustin <aymeric.augustin@…>):
In [e69348b4e7f07ef927edaecc7126901fc91c79d0]:
{{{
#!CommitTicketReference repository=""
revision="e69348b4e7f07ef927edaecc7126901fc91c79d0"
Avoided mixing dates and datetimes in the examples.
Refs #16023.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/16023#comment:8>
Django
Reporter: jodym@… | Owner: Aymeric
| Augustin
Type: Bug | Status: closed
Component: Documentation | Version: 1.4
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by suhailvs):
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))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/16023#comment:9>