Convert timezone in django annotate with date and time fields

django_annotate_date_time_convert_tz.py

# Insane Django operation I had to do
# Inspired by: https://stackoverflow.com/a/70552577/8927098


# Need to filter for a date, but source table date is in UTC and it's separated into date and time fields
# Example:
# Date to find (America/New_York): 2005-09-08
# Date in table (UTC): 2005-09-09
# Time in table (UTC): 01:07:00

# So converting what's in the table from UTC to America/New_York gives
# Date in table (America/New_York): 2005-09-08
# Time in table (America/New_York): 21:07:00

# Here's a way (probably not the best way) to do this:


from django.db.models import Func, ExpressionWrapper, F, DateTimeField, DateField


class AtTimeZone(Func):
    function = "AT TIME ZONE"
    template = "(%(expressions)s %(function)s 'UTC') %(function)s 'America/New_York'"
    

YourModel.objects.annotate(
    # Add date and time fields
    datetime=ExpressionWrapper(
        F("date") + F("time"), output_field=DateTimeField()
    ),
    # Run AtTimeZone Func on datetime field
    datetime_et=AtTimeZone(F("datetime")),
).filter(
    # Use __date here to get back to date field 
    datetime_et__date=datetime(2005, 9, 8)
)