Django & Postgresql annotate datetime field to localtime

Question

I'm using Django 1.11 with Postgresql 9.6 and JQuery DataTables (using django-datatables to provide AJax datasource from Djang model.

Model example:

class MyModel(models.Model):
    start = models.DateTimeField()
    end = = models.DateTimeField()

Postgresql stores the datetimes in UTC with timezone info, which is fine.

I can override the DataTables render column to render the datetime correctly in the DataTables view:

if column == "start":
    return timezone.localtime(row.start).strftime(STRFTIME_DATETIME_FORMAT)

The problems when trying to provide the search filter query for partial dates. If I add an annotation to add date_str to search on:

def filter_queryset(self, qs):
    search = self.request.GET.get(u'search[value]', None)
    if search:
        sql_datetime_format = getattr(settings, "SQL_DATETIME_FORMAT", "DD/MM/YYYY HH24:MI")
        qs = qs.annotate(
            start_str=Func(F('start'), Value(sql_datetime_format), function='to_char'),
            end_str=Func(F('end'), Value(sql_datetime_format), function='to_char'),
            )
        q_objects = Q()
        q_objects |= Q(start_str__icontains=search)
        q_objects |= Q(end_str__icontains=search)
        qs = qs.filter(q_objects).distinct()
    return qs

The start_str and end_str are converted to strings as UTC datetimes not local datetimes.

So I UK date in summer displays correctly as 01/06/2017 00:00, but to search for it you have to enter: 31/05/2017 23:00

I can't seem to find away to get start_str and end_str to local time instead of UTC.

Steve · Accepted Answer · 2017-11-28 19:59:34Z

To deal with the date and time I ended up writing my own django expression for postgresql:

from django.db.models import Func

def ToChar(expression, output):
    '''
    Custom query to convert timestamp to string.

    Example usage
    queryset.annotate(
        created_date_str=ToChar('created_date', 'DD/MM/YYYY HH25:MI')
        )
    '''

    class ToCharWithoutTZ(Func):

        function = "TO_CHAR"
        template = '%(function)s(%(expressions)s, \'{output}\')'.format(output=output)

    return ToCharWithoutTZ(expression)

def ToCharTZ(expression, timezone, output):
    '''
    Custom query to convert timestamp to string in requested time zone.

    Example usage
    queryset.annotate(
        created_date_str=ToCharTZ('created_date', 'GB', 'DD/MM/YYYY HH25:MI')
        )
    '''

    class ToCharWithTZ(Func):

        function = "TO_CHAR"
        template = '%(function)s(%(expressions)s AT TIME ZONE \'{timezone}\', \'{output}\')'.format(timezone=timezone, output=output)

    return ToCharWithTZ(expression)

Example usage:

from myapp.models.functions import ToCharTZ

def filter_queryset(self, qs):
    search = self.request.GET.get(u'search[value]', None)
    if search:
        sql_datetime_format = getattr(settings, "SQL_DATETIME_FORMAT", "DD/MM/YYYY HH24:MI")
        qs = qs.annotate(
            start_str=ToCharTZ('start', 'GB', sql_datetime_format),
            end_str=ToCharTZ('end', 'GB', sql_datetime_format),
        )
    q_objects = Q()
    q_objects |= Q(start_str__icontains=search)
    q_objects |= Q(end_str__icontains=search)
    qs = qs.filter(q_objects).distinct()
    return qs

Steve · Accepted Answer · 2017-11-03 16:43:42Z

0

With some help from #django IRC channel I've solved this by using DateTZ in the django-pg-utils package:

To install:

pip install django-pg-utils

Updated code snippit:

from pg_utils.utils import DateTZ

def filter_queryset(self, qs):
    search = self.request.GET.get(u'search[value]', None)
    if search:
        sql_datetime_format = getattr(settings, "SQL_DATETIME_FORMAT", "DD/MM/YYYY HH24:MI")
        qs = qs.annotate(
            start_str=Func(DateTZ(F('start'), 'GB'), Value(sql_datetime_format), function='to_char'),
            end_str=Func(DateTZ(F('end'), 'GB'), Value(sql_datetime_format), function='to_char'),
        )
    q_objects = Q()
    q_objects |= Q(start_str__icontains=search)
    q_objects |= Q(end_str__icontains=search)
    qs = qs.filter(q_objects).distinct()
return qs

Hopefully this will be useful to others.

answered Nov 3, 2017 at 16:43

Steve

1413 silver badges14 bronze badges

1 Comment

Steve Over a year ago

This didn't pull back the time info, DateTZ only works with the date part, see next answer.

Collectives™ on Stack Overflow

Django & Postgresql annotate datetime field to localtime

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related