id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
29884	Queryset.filter with TruncBase functions not working as expected when USE_TZ= True	slide333333	nobody	"
** 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-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."	Uncategorized	new	Database layer (models, ORM)	2.1	Normal				Unreviewed	0	0	0	0	0	0