Union of TruncBase annotations with different tzinfo apply `convert_value` of last tzinfo.

[Jurgis Pralgauskis]

class Message(models.Model):
    timestamp = models.DateTimeField(auto_now=True) # PostgreSQL, USE_TZ=True
    msg = models.CharField(max_length=254)
    timezone = models.CharField(max_length=64, default="UTC", help_text="pytz name")

    def test_demo_bug(self):
        from ..models import Message
        import pytz
        from django.db.models.functions import Trunc, TruncSecond
        import mock
        from django.utils import timezone

        with patch.object(timezone, 'now', return_value=pytz.utc.localize(datetime(2017, 1, 1, 0, 0))):

            # we have info
            Message.objects.create( msg = "bar", timezone = 'UTC')
            Message.objects.create( msg = "foo", timezone = 'America/Los_Angeles')


            # we want to get it with "localized" timestamps:
            qs = Message.objects.all()

            partitions = []  # partition by timezones
            for tzname in ['UTC', 'America/Los_Angeles']:
                tz = pytz.timezone(tzname)
                _qs = qs.filter(timezone=tzname)  # was but in test hardcoded "UTC" instead of `tzname`
                _qs = _qs.annotate(trunc_local_time=TruncSecond('timestamp', tzinfo=tz)) # could be Trunc_anything_
                partitions.append(_qs)

            qs1, qs2 = partitions

            result = list(qs1.union(qs2))

            for x in result:
                tz = pytz.timezone(x.timezone)
                print(x.msg)
                x.timestamp_trunc = x.timestamp.replace(microsecond=0)
                x.expected = x.timestamp_trunc.astimezone(tz)  # this is the working way  (but I wanted to get localization in DB layer)
                assert x.trunc_local_time == x.expected,  "Error (msg: '{x.msg}'): {x.trunc_local_time} != {x.expected}".format(x=x)

Problem - different TZ offsets

Failure
Traceback (most recent call last):
  File "/home/jurgis/dev/new/tableair/sync_tableair-cloud/ta/api/bookables/tests/test_endpoints.py", line 689, in test_demo_bug
    assert x.trunc_local_time == x.expected,  "Error (msg: '{x.msg}'): {x.trunc_local_time} != {x.expected}".format(x=x)
AssertionError: Error (msg: 'foo'): 2016-12-31 16:00:00+00:00 != 2016-12-31 16:00:00-08:00

[Jurgis Pralgauskis]

related idea, using CASE/WHEN, gives localized (but naive) datetime (because it just don't apply BaseTrunc.convert_value
​https://mjec.blog/2016/06/27/djangos-queryset-union-isnt-quite-what-it-seems/

[Jurgis Pralgauskis]

workarounds are:

• to take initial query and later adapt

  tz = pytz.timezone(x.timezone)
  x.timestamp.astimezone(tz)
  

• or just make separate queries per timezone and chain (kind of union) them in python

[Mariusz Felisiak]

Thanks for the report, however ticket description is not correct. Django uses tzinfo properly in your use case:

SELECT
    ...
    DATE_TRUNC('second', "ticket_30575_message"."timestamp" AT TIME ZONE 'UTC') AS "trunc_local_time",
    UTC AS "tzname"
FROM "ticket_30575_message" WHERE "ticket_30575_message"."timezone" = UTC
UNION
SELECT
    ...
    DATE_TRUNC('second', "ticket_30575_message"."timestamp" AT TIME ZONE 'America/Los_Angeles') AS "trunc_local_time",
    America/Los_Angeles AS "tzname"
FROM "ticket_30575_message" WHERE "ticket_30575_message"."timezone" = UTC)

i.e. UTC in a qs1 and America/Los_Angeles in qs2. You can add tzname to you queryset (e.g. tzname=Value(tzname, output_field=CharField())) to check that everything works properly:

>>> for x in result:
>>>     print(x.timestamp, x.trunc_local_time, x.tzname)
2019-06-18 10:07:04.111245+00:00 2019-06-18 03:07:04+00:00 America/Los_Angeles 
2019-06-18 10:07:04.111245+00:00 2019-06-18 10:07:04+00:00 UTC 

Closing per TicketClosingReasons/UseSupportChannels.

[Jurgis Pralgauskis]

Sorry, was a bug in my test, and failure didn't show up (probalby copied wrong revision here)..

The SQL is generated OK, but imo, the problem is that TruncBase#convert_value has the lines

                value = value.replace(tzinfo=None)   
                value = timezone.make_aware(value, self.tzinfo)  

they apply (replace) the same tzinfo to all (union'ed) rows (that come from different timezones)

[Mariusz Felisiak]

Thanks for an extra clarification, I'm not sure if it is feasible with the current implementation of combined queries, because on Python's side we may not be able to recognize which row is from which query. I will confirm this later.

[Mariusz Felisiak]

Thanks again for detailed reported, unfortunately you found an edge case that is not fixable. Django gets naive datetimes from the database and makes them aware with tzinfo argument from e.g. TruncSecond function, however with combined queries we use (if required) functions from the first query because we are not be able to recognize which row is from which query.