#29884: Queryset.filter with TruncBase functions not working as expected when USE_TZ= True -------------------------------------+------------------------------------- Reporter: slide333333 | Owner: nobody Type: Uncategorized | Status: new Component: Database layer | Version: 2.1 (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 -------------------------------------+------------------------------------- Description changed by slide333333:
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:3> 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 django-updates+unsubscr...@googlegroups.com. To post to this group, send email to django-updates@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.c3a97385c35cd11841d131befb6fa1fc%40djangoproject.com. For more options, visit https://groups.google.com/d/optout.