How can I cast a DateField() + TimeField() to local time in a Django QuerySet?

Question

My model as these fields:

• date = models.DateField()
• start_time = models.TimeField()
• end_time = models.TimeField()

I would like to annotate the queryset with start_datetime and end_datetime, like so:

class SessionQuerySet(models.QuerySet):
    def with_datetimes(self):
        return self.annotate(
            start_datetime=ExpressionWrapper(
                F('date') + F('start_time'),
                output_field=models.DateTimeField()
            ),
            end_datetime=ExpressionWrapper(
                F('date') + F('end_time'),
                output_field=models.DateTimeField()
            ),
        )

However, the output field in the query results in a naive datetime:

>>> Session.objects.with_datetimes()[0].start_datetime
<<< datetime.datetime(2021, 9, 20, 17, 0)

I would like the dates to be localized within the query.

I tried wrapping the above expressions in django.db.models.functions.Cast(), with output_field=DateTimeField(), but it casts to UTC and not the local timezone.

Essentially what I need is the equivalent of the Postgres at time zone feature to convert a naive time to localtime. Is there a way to do that in Django?

Answer 1

Yes. You can use any Postgres function by writing a custom django database function.

Here is a custom django database function for the equivalent of the Postgres at time zone.

Django 4.0

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


class AtTimeZone(Func):
    function = 'AT TIME ZONE'
    template = "%(expressions)s %(function)s '%(timezone)s'"


class SessionQuerySet(models.QuerySet):
    def with_datetimes(self):
        return self.annotate(
            start_datetime=ExpressionWrapper(
                F('date') + F('start_time'),
                output_field=models.DateTimeField()
            ),
            end_datetime=ExpressionWrapper(
                F('date') + F('end_time'),
                output_field=models.DateTimeField()
            ),
            start_local_datetime=AtTimeZone(F('start_datetime', timezone='Europe/Berlin')
        )

The above is for a model with the initial fields of: date, start_time, and end_time.

Here are the docs regarding django's custom database functions. https://docs.djangoproject.com/en/4.0/ref/models/expressions/#func-expressions. As of the start of 2022, the docs don't provide many examples on how to create custom database functions. This should help.