Queryset.filter with TruncBase functions not working as expected when USE_TZ= True

[slide333333]

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.