#34699: Filtering on annotated TruncSecond expression gives unexpected result.
-------------------------------------+-------------------------------------
Reporter: Stefan | Owner: Francesco
Type: | Panico
Cleanup/optimization | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
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
-------------------------------------+-------------------------------------
Comment (by Stefan):
@Natalia, apologies I should've said, it was PSQL 14.3.
I want to clarify that from a purely ORM perspective the result is
surprising, given
{{{#!python
Book.objects.update(published=timezone.now())
Book.objects.annotate(_published_trunc=TruncSecond('published')).filter(_published_trunc__lte=timezone.now()).count()
# 0
}}}
the result is 0. One has to dive in to the SQL to understand why this is
happening, and it's because `AT TIME ZONE 'Europe/Berlin'` is making the
timezone naive in the DB level. The docs do say:
Trunc() - "If a different timezone like Australia/Melbourne is active
in Django, then the datetime is converted to the new timezone before the
value is truncated."
Although it says the datetime is converted to the new timezone before the
value is truncated, ''converted to new timezone'' doesn't necessarily
suggest that the resulting time zone is naive - one could still assume it
an aware timezone just with an offset of +11:00. The latter is further
suggested because the immediately following examples in that doc show the
returned value on the annotation are an aware value with an offset of
+11:00, and not naive.
--
Ticket URL: <https://code.djangoproject.com/ticket/34699#comment:11>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070189643eb452-5d5fb5fb-986b-4497-beb1-845bc1d76e79-000000%40eu-central-1.amazonses.com.
