Range lookup with complex expressions

Using Django Using the ORM
1

When using __range with simple values it works as expected, for example:

FooModel.objects.filter(some_date_field__range=(datetime(2000,1,1), datetime(2010,1,1)))

But if you use expressions of any sort, including just referencing fields with F(), it seems to just convert the expression to a string representation, which of course fails. For example:

FooModel.objects.filter(some_date_field__range=(expressions.Value(datetime(2000,1,1)), expressions.Value(datetime(2010,1,1))))

Generates SQL that looks like this “BETWEEN ‘Value(datetime.datetime(2000,1,1))’ AND ‘Value(datetime.datetime(2010,1,1))’”, which is obviously not correct.

Is there a way to get __range to accept expressions? Or do I have to use __lte and __gte instead?

2

I think we’re going to need a more specific example, because when I wrap a datetime object in the Value function, it works for me.

3

In shell_plus:

str(Person.objects.filter(date_of_birth__range=(Value(datetime.datetime(1900,1,1)), Value(datetime.datetime(2010,1,1)))).values('date_of_birth').query)

Outputs:

'SELECT "org_person"."date_of_birth" FROM "org_person" WHERE "org_person"."date_of_birth" BETWEEN Value(datetime.datetime(1900, 1, 1, 0, 0)) AND Value(datetime.datetime(2010, 1, 1, 0, 0))'

And running the query produces no results.

But using:

Person.objects.filter(date_of_birth__range=(datetime.datetime(1900,1,1), datetime.datetime(2010,1,1))).values('date_of_birth')

Produces 20+ results.

And stringifying the query

str(Person.objects.filter(date_of_birth__range=(datetime.datetime(1900,1,1), datetime.datetime(2010,1,1))).values('date_of_birth').query)

Produces

'SELECT "org_person"."date_of_birth" FROM "org_person" WHERE "org_person"."date_of_birth" BETWEEN 1900-01-01 AND 2010-01-01'

I’m using Django 4.2.7 in case that may matter.

4

That’s interesting, because that doesn’t happen with me.

The following is done in shell_plus

First, the data:

In [64]: [(x.id, x.dt, x.name) for x in Race.objects.all().order_by("id")]
Out[64]: 
[(1, datetime.date(2023, 5, 6), 'Kentucky Derby'),
 (2, datetime.date(2023, 5, 20), 'Preakness'),
 (3, datetime.date(2023, 6, 10), 'Belmont')]

In [65]: [(x.id, x.name, x.dt) for x in Race.objects.filter(dt__range=(datetime.date(2023,5,1),datetime.date(2023,5,31)))]
Out[65]: 
[(1, 'Kentucky Derby', datetime.date(2023, 5, 6)),
 (2, 'Preakness', datetime.date(2023, 5, 20))]

In [67]: [(x.id, x.name, x.dt) for x in Race.objects.filter(dt__range=(Value(datetime.date(2023,5,1)),Value(datetime.date(2023,5,31))))]
Out[67]: 
[(1, 'Kentucky Derby', datetime.date(2023, 5, 6)),
 (2, 'Preakness', datetime.date(2023, 5, 20))]


In [68]: print(Race.objects.filter(dt__range=(datetime.date(2023,5,1),datetime.date(2023,5,31))).query)
SELECT "df_race"."id", "df_race"."dt", "df_race"."name" FROM "df_race" WHERE "df_race"."dt" BETWEEN 2023-05-01 AND 2023-05-31

and

In [69]: print(Race.objects.filter(dt__range=(Value(datetime.date(2023,5,1)),Value(datetime.date(2023,5,31)))).query)
SELECT "df_race"."id", "df_race"."dt", "df_race"."name" FROM "df_race" WHERE "df_race"."dt" BETWEEN 2023-05-01 AND 2023-05-31
5

Just out of curiosity, what database engine are you using?

6

This is with Sqlite3.

7

I just confirmed, this is a Sqlite engine issue. I can recreate your results by using it as the database engine. From reading through the docs a bit more, this appears to make sense, given that Sqlite3 doesn’t have a “DateTime” data type. (See Section 3.2 at Quirks, Caveats, and Gotchas In SQLite)

8

I should probably submit a bug ticket then. I can work around it with lte/gte lookups. Or I could write a custom range lookup that does the right thing.

9

This issue is really interesting. It seems that it was fixed for most fields but DateField about 7 years ago and resulted in this commit which has some appreciable test in place.

If you want to have a look at submitting a bug report and a patch I suggest you have a look at the logic in charge of dealing with adapting date values. The problem should be evident by looking at how datetime and time values are dealt with compared to date ones with regards to the string conversion you are experiencing when dealing with expressions.

10

This PR should address your issue.

11

Thanks for the PR! We haven’t upgraded yet, but this will be one reason to do so. I appreciate the quick response and apologize for my long delay.