[Django]-Django MySQL group by day with timezone

by

3👍

✅

Ah, this was a good challenge. I was able to test from PostGres, and I can confirm it is working. The MySQL code should be pretty close. However, there is a note on the CONVERT_TZ documentation:

To use named time zones such as MET or Europe/Moscow, the time zone tables must be properly set up. See Section 10.6, “MySQL Server Time Zone Support”, for instructions.

MySQL (using CONVERT_TZ(dt, from_tz, to_tz))

from_tz = 'UTC'
to_tz = 'Australia/ACT'
report = Sale.objects.extra(
    {
        'day': "date(CONVERT_TZ(sale_date, '{from_tz}', '{to_tz}'))".format(
            from_tz=from_tz,
            to_tz=to_tz
         )
    }
).values(
    'day'
).annotate(
    day_total=Sum('total')
)

PostGres: (using AT TIME ZONE)

time_zone = 'Australia/ACT'
report = Sale.objects.extra(
    {'day': "date(sale_date) AT TIME ZONE '{0}'".format(time_zone)}
).values(
    'day'
).annotate(
    day_total=Sum('total')
)
👤Michael B

Leave a comment