Query may fail with pytz exception

[lvella]

If I give date object as input to a filter on a DateTimeField field, like this:

class Ticket(models.Model):
    register_time = models.DateTimeField(auto_now_add=True)

day = datetime.strptime('2014-10-19', '%Y-%m-%d').date()
Ticket.objects.filter(register_time__gte=day)

I may get a pytz.exceptions.NonExistentTimeError exception. The exact exception was:

pytz.exceptions.NonExistentTimeError: 2014-10-19 00:00:00

This date is the start of DST in my timezone:

TIME_ZONE = 'America/Sao_Paulo'

I believe this is a bug because Django tries to convert my input to a datetime object before building the query, and in this case, this conversion yields an invalid datetime.

This bug seems very related to issue #9596, because in these cases, datetime should be converted to date, not the opposite.

A minimal showcase is attached. Unzip and run ./manage.py test

[lvella]

Minimal showcase.

[Aymeric Augustin]

That's the intended behavior -- Django won't guess what you mean if you pass invalid inputs.

However, if #9596 gets fixed with the idea of comment 24, you will be able to run Ticket.objects.filter(register_time__date__gte=day).

Until then, you're stuck with comparing with an aware datetime object. See ​https://www.python.org/dev/peps/pep-0431/#ambiguous-times for why this is awfully complicated.

[lvella]

Replying to aaugustin:

That's the intended behavior -- Django won't guess what you mean if you pass invalid inputs.

But if using date to compare with datetime works 99% of the time, and there is no big warning in the documentation, saying that it must not be done, it clearly violates the principle of least astonishment.

In such cases, either it should always work, or should always fail with an error. And it is not that hard to make it work, for instead of trying to add time information to the date before passing it to the database, the database type should be converted to date, dropping the time information in the process.

Actually, my workaround in the case was to force Django to deliver my input in SQL as date, not as datetime:

q = Ticket.objects.filter(register_time__gte=day + (F('id') - F('id')))
print(q.query)

yields:

SELECT "reproduce_ticket"."id", "reproduce_ticket"."register_time" FROM "reproduce_ticket" WHERE "reproduce_ticket"."register_time" >=  2014-10-19 + ("reproduce_ticket"."id" - "reproduce_ticket"."id")

and it worked!
Compare with the original query (with another date so it won't raise the exception):

q = Ticket.objects.filter(register_time__gte=day)
print(q.query)

SELECT "reproduce_ticket"."id", "reproduce_ticket"."register_time" FROM "reproduce_ticket" WHERE "reproduce_ticket"."register_time" >= 2014-11-19 02:00:00

[Aymeric Augustin]

Thanks for the proposal. Unfortunately it won't work on databases other than PostgreSQL because they store datetimes in UTC. As a consequence they're vulnerable to the effect I described in question 3 in the ​timezone troubleshooting FAQ.

I considered this issue carefully when I implemented support for time zones in Django 1.4. I was aware of the points you're making. I decided that forbidding mixing dates and datetimes outright was too extreme. It would require many changes in projects that work just fine because they never see datetimes during the DST switch.

It's the left-hand-side of a lookup that determines the type, not the right-hand-side. Therefore the correct solution is to implement an explicit __date lookup i.e. #9596. That's the opposite of what you requested when you filed the ticket but that also what you recommended in your last comment:

the database type should be converted to date, dropping the time information in the process

This solution requires timezone conversion of datetimes in the database but it shouldn't be an issue as it already exists to support the dates() and datetimes() queryset methods.

I'm going to requalify this as a documentation issue because comparing dates with datetimes is probably a common error and I don't think the docs warn against it. I don't know where the warning should be added, though.

[Adam Zapletal]

I wonder if this issue can be closed with no documentation changes for the following reasons:

1. The __date__gte lookup (mentioned in comment 1) and others for DateTimeField have been implemented for a long time.
2. pytz has been removed as of Django 5.0.
3. Support for time zones has now been core to Django for almost 12 years.
4. The documentation for the lookup mentioned above seems sufficient to me to help future users wondering how to compare a DateTimeField with a datetime.date: ​https://docs.djangoproject.com/en/dev/ref/models/querysets/#date.

However, I'd be happy to work on a documentation patch if someone can provide an idea of what is needed to close this ticket.

[Mariusz Felisiak]

The following comment by Aymeric is crucial, and, as far as I'm aware, it's not fixed:

I'm going to requalify this as a documentation issue because comparing dates with datetimes is probably a common error and I don't think the docs warn against it. I don't know where the warning should be added, though.

[Adam Zapletal]

Thanks! I opened a PR with an attempt at documenting this. Please let me know if the content or location needs to be improved.

[Sarah Boyce <42296566+sarahboyce@…>]

In 99273fd:

Fixed #24076 -- Added warnings on usage of dates with DateTimeField and datetimes with DateField.

[Sarah Boyce <42296566+sarahboyce@…>]

In bf9a89f:

[5.1.x] Fixed #24076 -- Added warnings on usage of dates with DateTimeField and datetimes with DateField.

Backport of 99273fd525129a973639044dfb12cfd732d8f1d6 from main.