Opened 5 years ago

Last modified 5 years ago

#30575 closed Bug

Union of TruncBase annotations with different tzinfo apply `convert_value` of last tzinfo (PostgreSQL) — at Version 4

Reported by: Jurgis Pralgauskis Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: timezone, TruncBase, Union, PostgreSQL
Cc: Jurgis Pralgauskis Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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='UTC')
                _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)

Failure
Traceback (most recent call last):
  File "/home/jurgis/dev/new/tableair/sync_tableair-cloud/ta/api/booking/tests/test_endpoints.py", line 1107, 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: 'bla'): 2016-12-31 16:00:00+00:00 != 2017-01-01 00:00:00+00:00}}}

Change History (4)

comment:1 by Jurgis Pralgauskis, 5 years ago

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/

comment:2 by Jurgis Pralgauskis, 5 years ago

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

comment:3 by Jurgis Pralgauskis, 5 years ago

Cc: Jurgis Pralgauskis added
Type: UncategorizedBug

comment:4 by Jurgis Pralgauskis, 5 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top