Using SQLAlchemy Custom Types to Convert Integers to DateTime

Today I was working on fetching out some data from an existing PostgreSQL server and generating some BSON output that would later be imported in to MongoDB. One of the problems I ran in to was that I needed to format the timestamps easily for each row of data.

Searching the internet I ran across this blog post by Ralph Bean, which does just that, but at a level that was well beyond what I needed. So taking away some inspiration from Ralph's blog post, I decided to just go with a Custom Type.

from time import mktime
from datetime import datetime

class IntegerDateTime(types.TypeDecorator):
    """Used for working with epoch timestamps.

Converts datetimes into epoch on the way in.
    Converts epoch timestamps to datetimes on the way out.
    """
    impl = types.INTEGER
    def process_bind_param(self, value, dialect):
        return mktime(value.timetuple())
    def process_result_value(self, value, dialect):
        return datetime.fromtimestamp(value)

Then in my reflected table, I just override the column that holds the integer representation of the datetime I want.

group_table = sa.Table('groups', metadata,
    sa.Column('register_time', IntegerDateTime),
    autoload=True,
    include_columns=[
        'group_id',
    'register_time',
    'type'
    ],
)

Now when we query and begin to use our results, register_time will be a DateTime object making it very easy to do any timedelta arithmetic or string formatting.