#29884: QuerySet.filter() with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------
Reporter: slide333333 | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 2.1
(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
-------------------------------------+-------------------------------------
Changes (by Ülgen Sarıkavak):
* cc: Ülgen Sarıkavak (added)
Old description:
> ** 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-24T00:00:00+02:00'::timestamptz LIMIT 21;
> args=('datetime.datetime(2018, 10, 24, 0, 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-24T00: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.
New description:
** 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-24T00:00:00+02:00'::timestamptz LIMIT 21;
args=('datetime.datetime(2018, 10, 24, 0, 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-24T00: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#comment:13>
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/0107018e3187af51-0f38baa4-d8d6-4cae-9c4f-d016c3619a15-000000%40eu-central-1.amazonses.com.
