#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 [email protected].
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.
