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.