#34699: Filtering on annotated TruncSecond expression gives unexpected result.
-------------------------------------+-------------------------------------
Reporter: Stefan | Owner: Wes P.
Type: | Status: assigned
Cleanup/optimization |
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Jacob Walls):
After "discovering" these issues for myself in a
[https://github.com/django/django/pull/20502 related PR], I agree that we
primarily have a documentation issue (or issues) to address.
> But then, at least to me, is quite surprising that TruncSecond, which is
an operation fully occurring in the DB, would not "respect" that "UTC
invariant" and have the TIME_ZONE setting affecting the results. Does my
point make sense? Do you have historic information about why TruncSecond
(and related functions) would not operate with/keep the tz defined in the
datetime being processed?
My guess is simply that it would be a useful convenience for `USE_TZ =
False` projects, which was the ancient default. There's no reason to
expect database data to be in UTC if `TIME_ZONE = 'America/Chicago'`
(default) and `USE_TZ = False`, so you would probably want this feature:
> If you switch from `USE_TZ = False` to `USE_TZ = True`, you must convert
your data from local time to UTC -- which isn't deterministic if your
local time has DST.
(It may have also been needed before 1.9 when there were
[https://docs.djangoproject.com/en/6.0/releases/1.9/#removal-of-time-zone-
aware-global-adapters-and-converters-for-datetimes global time zone
adapters]?)
----
> My PR basically adds a warning in the documentation when using Trunc
functions as a filter because it can have unexpected behavior.
That sounds good. It's really easy to compare local times against UTC
times by doing `my_trunc=datetime.now()` (while `USE_TZ = True`) like in
the original report.
Notice that for `USE_TZ=False`, `Trunc`
[https://github.com/django/django/blob/2b192bff26cf956c168790fce6a637cbd814250b/tests/db_functions/datetime/test_extract_trunc.py#L105
tests] routinely call `make_aware()` on the right-hand sides to correct
for this, comparing local to local:
{{{#!py
@override_settings(USE_TZ=False)
class DateFunctionTests(TestCase):
...
def test_extract_year_lessthan_lookup(self):
start_datetime = datetime.datetime(2015, 6, 15, 14, 10)
end_datetime = datetime.datetime(2016, 6, 15, 14, 10)
if settings.USE_TZ:
start_datetime = timezone.make_aware(start_datetime)
end_datetime = timezone.make_aware(end_datetime)
}}}
The
[https://github.com/django/django/blob/2b192bff26cf956c168790fce6a637cbd814250b/tests/db_functions/datetime/test_extract_trunc.py#L1685
subsequent tests] test `UZE_TZ=True` with `TIME_ZONE="UTC"`, which is not
the default (default is `America/Chicago`):
{{{#!py
@override_settings(USE_TZ=True, TIME_ZONE="UTC")
class DateFunctionWithTimeZoneTests(DateFunctionTests):
def test_extract_func_with_timezone(self):
start_datetime = datetime.datetime(2015, 6, 15, 23, 30, 1, 321)
end_datetime = datetime.datetime(2015, 6, 16, 13, 11, 27, 123)
start_datetime = timezone.make_aware(start_datetime)
end_datetime = timezone.make_aware(end_datetime)
...
melb = zoneinfo.ZoneInfo("Australia/Melbourne")
qs = DTModel.objects.annotate(
day=Extract("start_datetime", "day"),
day_melb=Extract("start_datetime", "day", tzinfo=melb),
...
}}}
----
My recommendation is that we close this ticket by:
1. Updating the docs for `Extract()` and `Trunc()` with some nuance
2. Fixing the Melbourne details from comment:19
3. Add at least one test for `Extract` or `Trunc` with `USE_TZ = True` and
`TIME_ZONE` != `UTC` (e.g. the default `America/Chicago`)
I'll review the PR with that in mind (it looks already well-scoped to
those points).
--
Ticket URL: <https://code.djangoproject.com/ticket/34699#comment:23>
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 visit
https://groups.google.com/d/msgid/django-updates/0107019bb41db37a-9905e6bd-f50e-4412-8758-1dcb57acb179-000000%40eu-central-1.amazonses.com.
