django ms-sql datetimeoffset

My current project has me dealing with python, which is a language I’ve dabbled with for many years, but I think this is the first time using it professionally. It’s quite interesting seeing where the language has evolved: I recall having previously been enamored with the quasi-Lisp approach of processing lists with map and filter, but have found list and dictionary comprehensions to be the current standard.

The project is a small Django API (if I were selling it to a VC then it’d be called a microservice :rolleyes:) and as the back-end is predominately in the Microsoft stack, it references a SQL Server database. This database includes some DateTimeOffset columns. I’m not sure I see the need for this type – dates should always be stored as UTC for maximum portability, and clients can display the local time based on client settings. If it’s necessary for a service to work with those dates, then the database should store a user timezone name or offset, but that is specific to the user and not the date. Anyway, I digress… Unfortunately DateTimeOffset columns are not natively supported by the common python ODBC connectors, and thus something of a workaround was required.

This was made extra challenging by Django, which intermediates the database relationship via its models, and therefore thwarted some early attempts to treat the columns as bytes. What it does expose is a connection_created signal which allows the connection to be intercepted before it is used, and that connection includes a method add_output_converter for handling ODBC types. In this case the type is -155 and using a little struct magic we can construct a python datetime.

One area of concern was ensuring that the signal handling was tidied up, even if exceptions were thrown. To handle this, the DateTimeOffset handling code was wrapped into a class that supports the with statement.

Apologies in advance if some of this python code is highly naive – as already noted – it’s my first professional python foray 🙂

import struct
import datetime
from django.db.backends.signals import connection_created

class DjangoSqlDateTimeOffset(object):

    def __enter__(self):

    def __exit__(self, exc_type, exc_value, traceback):
        # to see connection info, including queries, uncomment and look at cnx with DEBUG = True 
        # cnx = connections['qs-sql']

    def on_connection_created(self, sender, **kwargs):
        conn = kwargs['connection']
        conn.connection.add_output_converter(-155, self.handle_datetimeoffset)

    def handle_datetimeoffset(self, dto_value):
        tup = struct.unpack("<6hI2h", dto_value)  # e.g., (2017, 3, 16, 10, 35, 18, 0, -6, 0)
        tweaked = [tup[i] // 10000 if i == 6 else tup[i] for i in range(len(tup))]
        dto_string = "{:04d}-{:02d}-{:02d} {:02d}:{:02d}:{:02d}.{:05d} {:+03d}{:02d}".format(*tweaked)
        return datetime.datetime.strptime(dto_string, '%Y-%m-%d %H:%M:%S.%f %z') 

With that class available, querying DateTimeOffset columns becomes nice and simple:

with DjangoSqlDateTimeOffset():
  item = ModelName.objects.raw('SELECT ... ')