Code

Opened 3 years ago

Closed 20 months ago

Last modified 20 months ago

#16023 closed Bug (invalid)

Range query on a datetime is NOT inclusive with dates

Reported by: jodym@… Owned by: aaugustin
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.

Attachments (0)

Change History (8)

comment:1 follow-up: Changed 3 years ago by aaugustin

  • Component changed from Database layer (models, ORM) to Documentation
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

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.

comment:2 in reply to: ↑ 1 Changed 3 years ago by 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().

comment:3 Changed 2 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:4 Changed 2 years ago by bubalanisaipriyan@…

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

comment:5 Changed 22 months ago by stu.axon@…

  • Version changed from 1.3 to 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 Changed 22 months ago by aaugustin

  • Owner changed from nobody to aaugustin

comment:7 Changed 20 months ago by aaugustin

  • Resolution set to invalid
  • Status changed from new to 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.

Last edited 20 months ago by aaugustin (previous) (diff)

comment:8 Changed 20 months ago by Aymeric Augustin <aymeric.augustin@…>

In [e69348b4e7f07ef927edaecc7126901fc91c79d0]:

Avoided mixing dates and datetimes in the examples.

Refs #16023.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.