#34699: Filtering on annotated TruncSecond expression gives unexpected result.
-------------------------------------+-------------------------------------
Reporter: Stefan | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Abdulla):
I believe this would be the case for all other Trunc Date expressions as
they are subclassed with `TruncBase`.
Replying to [ticket:34699 Stefan]:
> With a non-UTC time zone:
>
> {{{#!python
> TIME_ZONE = 'Europe/Berlin'
> }}}
>
> and a simply query like so
>
> {{{#!python
> from django.db.models.functions import TruncSecond
> from django.utils import timezone
>
> book = Book.objects.get(id=2)
> now = timezone.now()
> book.published = now
> book.save()
>
Book.objects.annotate(_published_trunc=TruncSecond('published')).filter(id=2,
_published_trunc__lte=now)
> }}}
>
> The result is empty; I have simply filtered `now` against a second-
trunced version of `now` so I would expect a result.
>
> However under the hood the `_published_now` column is converted to a
naive timestamp using `AT TIME ZONE 'Europe/Berlin'` and is thus a naive
timestamp 2 hours ''ahead'' of UTC.
>
> {{{#!sql
> SELECT "book"."id",
> "book"."published",
> DATE_TRUNC('second', "book"."published" AT TIME ZONE
'Europe/Berlin') AS "_published_trunc"
> FROM "book"
> WHERE ("book"."id" = 2 AND
> DATE_TRUNC('second', "book"."_published_trunc" AT TIME ZONE
'Europe/Berlin') <=
> '2023-07-04 11:59:00+02:00'::timestamptz)
> }}}
>
>
> The filter compares a naive timestamp to an aware one, but assumes the
LHS naive timestamp is a UTC timestamp - which it is not, it is Berlin
time.
>
> **Workaround**
>
> 1) Use `TruncSecond(now)` in the filter so the compared naive timestamps
are the same.
>
> 2) Use `_published_trunc=TruncSecond('published',
tzinfo=datetime.timezone.utc)` - I don't like this though. It's not clear
without a comment why the tzinfo is needed ''and'' it assumes the database
will compare timezones using UTC.
--
Ticket URL: <https://code.djangoproject.com/ticket/34699#comment:2>
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/010701893228280c-d6873e19-29ef-4fc8-b388-311648278491-000000%40eu-central-1.amazonses.com.
