#29884: Queryset.filter with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
slide333333 |
Type: | Status: new
Uncategorized |
Component: Database | Version: 2.1
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
** Tested PostgreSQL only **
** USE_TZ=True **
Consider the following model:
{{{
class TimeStampModel(models.Model):
timestamp = models.DateTimeField()
}}}
Create some data:
{{{
TimeStampModel.objects.bulk_create([
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 5, 13,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 7, 4,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 8, 56,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 13, 49,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 15, 33,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 18, 29,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 19, 12,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 21, 37,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 21, 9,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 23, 23,
tzinfo=pytz.utc)),
])
}}}
The following code shoud definitely return the data. But it returns an
empty queryset, because the SQL generated is not correct.
{{{
>>> from django.db.models.functions import *
>>> from django.utils import timezone
>>> import datetime, pytz
>>> TimeStampModel.objects.annotate(
... day=TruncDay('timestamp',
tzinfo=pytz.timezone('Europe/Berlin'))).filter(
... day=timezone.make_aware(datetime.datetime(2018, 10, 24),
pytz.timezone('Europe/Berlin'))
... )
DEBUG: (0.000) SELECT "truncbase_timestampmodel"."id",
"truncbase_timestampmodel"."timestamp", DATE_TRUNC('day',
"truncbase_timestampmodel"."timestamp" AT TIME ZONE 'Europe/Berlin') AS
"day" FROM "truncbase_timestampmodel" WHERE DATE_TRUNC('day',
"truncbase_timestampmodel"."timestamp" AT TIME ZONE 'Europe/Berlin') =
'2018-10-23T22:00:00+02:00'::timestamptz LIMIT 21;
args=('datetime.datetime(2018, 10, 23, 22, 0, tzinfo=<DstTzInfo
'Europe/Berlin' CEST+2:00:00 DST>)')
<QuerySet []>
}}}
The SQL should be (note the additional `AT TIME ZONE 'Europe/Berlin'`).
{{{
SELECT "truncbase_timestampmodel"."id",
"truncbase_timestampmodel"."timestamp",
DATE_TRUNC('day', "truncbase_timestampmodel"."timestamp" AT TIME
ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin' AS "day"
FROM "truncbase_timestampmodel"
WHERE DATE_TRUNC('day', "truncbase_timestampmodel"."timestamp" AT TIME
ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin' =
'2018-10-23T22:00:00+02:00'::timestamptz
LIMIT 21;
}}}
https://www.postgresql.org/docs/9.2/static/functions-datetime.html
#FUNCTIONS-DATETIME-ZONECONVERT
This fix will also make sure that the returned value from the database
driver is an aware dateime. Currently the returned value is native and
manually made aware in
`django.db.models.functions.datetime.TruncBase.convert_value`!
I'm not sure how the problem relates to databases without timezone
support. But for those with time zone support like PostgreSQL this should
work as expected. For Postgres the fix should be pretty easy:
`django.db.backends.postgresql.operations.DatabaseOperations.datetime_trunc_sql`
has to be patched and
`django.db.models.functions.datetime.TruncBase.convert_value` should be
patched. The latter will also affect other database engines.
--
Ticket URL: <https://code.djangoproject.com/ticket/29884>
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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/054.7013cabd3c1bf9b4e812e492bebd1c72%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
