#34699: Filtering on annotated TruncSecond expression gives unexpected result.
-------------------------------------+-------------------------------------
Reporter: Stefan | Owner: Wes P.
Type: | Status: assigned
Cleanup/optimization |
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 Wes P.):
I did a little research into this and I would say Stefan and Mariusz are
both correct. The behavior of `TruncSecond` is unexpected but any changes
to the feature would break things for people who have used any Trunc
functions in a filter and have manually adjusted for the timezone naive
result. My proposal is a change to the documentation with a warning about
using any of the Trunc functions in a filter when the set timezone is not
UTC and to recommend a way of constructing the filter in such away as to
produce the expected results. I have a number of tests that confirm the
documented warning so that if someone changes the behavior in the future
the documentation should be updated.
The way that Django uses `DATE_TRUNC` function in Postgres leads us to
believe that the underlying filter will also be timezone aware, but the
way that Django calls `DATE_TRUNC` in Postgres returns a timezone naive
result (see: https://www.postgresql.org/docs/current/functions-
datetime.html#FUNCTIONS-DATETIME-TRUNC). Django returns a timezone aware
result by converting the naive value which is why you see the different
timezone values in your sample query earlier in the ticket.
The `TruncSecond` (and other Trunc functions) were added (I think) in 2013
and at that time, Postgres did not have the option for doing a timezone
aware `DATE_TRUNC` without doing it the way that Django is currently doing
it which returns a timezone naive result. In 2019 Postgres added a new
feature to the function that does return a timezone aware result so that
the filter in Stefan's situation would work as expected. I did play with
this and it would be possible to add this as a new feature, possibly in
the same way that Postgres has, by passing an optional parameter to the
TruncBase class that would access the feature so that it won't break
existing code.
--
Ticket URL: <https://code.djangoproject.com/ticket/34699#comment:14>
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/010701926e958b8d-d31c9846-1c48-49c6-96aa-97bfabc0c644-000000%40eu-central-1.amazonses.com.
