A JSONDateTime TypeDecorator

JSON doesn't provide a date or date-time construct; therefore every application is burdened with implementing a solution of it's own for receiving date-time information over the wire. On common issue receiving JSON and serializing that data into some type of database - but the database knows the value is a date-time and you want to be able to perform date-time like operations on the value (locally).

Fortunately if your database is accessed via an SQLAlchemy ORM you can out-source this deserialization of the the values into your ORM by creating a TypeDecorator that wraps the datetime value and knows how to recognize and parse date-time values.

This example will allow an actual date-time value to be assigned to the attribute, or a 19 or 16 character string, or a NULL (None). Conversion happens automatically when the value is assigned, but when the value is accessed an actual datetime is always seen.

    from datetime import datetime
    from sqlalchemy import TypeDecorator
    from sqlalchemy.types import DateTime

    class JSONDateTime(TypeDecorator):
        """ Allow storing a string into a datetime value, this allows 
            for automatically conversion of the JSON date strings into 
            date values"""
        impl = DateTime

        def __init__(self, *arg, **kw):
            TypeDecorator.__init__(self, *arg, **kw)

        def process_bind_param(self, value, dialect):
            if value:
                if isinstance(value, basestring):
                    if (len(value) == 19):
                        return datetime.strptime(value, '%Y-%m-%dT%H:%M:%S')
                    elif (len(value) == 16):
                        return datetime.strptime(value, '%Y-%m-%dT%H:%M')
                    elif (len(value) == 0):
                        return None
                elif isinstance(value, datetime):
                    return value
                raise Exception('Cannot store value "{0}" as DateTime'.format(value))
            return None

    # Create a class/table

    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base()

    class LogEntry(Base):
        __tablename__ = 'logentry'
        ...
        actiondate = Column(JSONDateTime)
        ...

One possible improvement is to make sure to assign a time-zone to the date value as comparing zoned and niave datetime values will raise an exception. But to do that you need to know the timezone you expect the JSON values to represent (in most cases this isn't present in the date-time string representation). The Python datetime's replace(tzinfo={tzinfoclass}) method is used to assign time zone information to a value. [Note: time zone information in Python.... not especially graceful or fun].