CODEWITHSUNDEEP
djangotimezonedjango-orm
Ozgur Akcali
Ozgur Akcali

Reputation: 5482

Django filter by hour of day when timezone info stored in database

With the following model structure:

class Place(models.Model):
    ...
    timezone = TimeZoneField()  # Defined in a package called django-timezone-utils
    ...

class Session(models.Model):
    ...
    place = model.ForeignKey(Place, ...)
    created = models.DateTimeField(auto_now_add=True)  # Saved in utc
    ...

I want to get all sessions that are created inside 10th hour of day. Basic approach to this is:

Session.objects.filter(created__hour=10)

But with this, as expected, results are filtered by UTC time. How can I get Sessions objects that are created inside the 10th hour of day, in the timezone of the Place they are connected to? Note that there could be sessions made on different Places with different timezones, but I want to get the sessions that are created inside the 10th hour of day in their local timezones.

Something like annotating each result with created time in local timezone, and then filtering on that annotated value might work, but I do not know how can I build that annotation

Upvotes: 2

Views: 662

Answers (1)

Ozgur Akcali
Ozgur Akcali

Reputation: 5482

I did what I wanted by defining a custom database function that converts a datetime field to a timezone, which is also defined at a related column. Works only with postgres now (making use of "at time zone" expression), but can be updated to use other db engines as well using their timezone conversion functions.

from django.db.models import Func, DateTimeField

class ConvertToTimezone(Func):
    """
    Custom SQL expression to convert time to timezone stored in database column
    """

    output_field = DateTimeField()

    def __init__(self, datetime_field, timezone_field, **extra):
        expressions = datetime_field, timezone_field
        super(ConvertToTimezone, self).__init__(*expressions, **extra)

    def as_sql(self, compiler, connection, fn=None, template=None, arg_joiner=None, **extra_context):
        params = []
        sql_parts = []
        for arg in self.source_expressions:
            arg_sql, arg_params = compiler.compile(arg)
            sql_parts.append(arg_sql)
            params.extend(arg_params)

        return "%s AT TIME ZONE %s" % tuple(sql_parts), params

And this is how I use it:

Session.objects.annotate(created_local=ConvertToTimezone('created', 'place__timezone')).filter(created_local__hour=10)

I do not have a strong understanding of how Django database functions and database expressions work, so am welcome to (and would be willing to hear) any feedbacks / improvements to the solution

Upvotes: 4

Related Questions