Defining a table with sqlalchemy with a mysql unix timestamp

Question

Background, there are several ways to store dates in MySQ.

As a string e.g. "09/09/2009".
As integer using the function UNIX_TIMESTAMP() this is supposedly the traditional unix time representation (you know seconds since the epoch plus/minus leap seconds).
As a MySQL TIMESTAMP, a mysql specific data type not the same than unix timestamps.
As a MySQL Date field, another mysql specific data type.

It's very important not to confuse case 2 with case 3 (or case 4). I have an existing table with an integer date field (case 2) how can I define it in sqlalchemy in a way I don't have to access mysql's "FROM_UNIXTIME" function?

For the record, just using sqlalchemy.types.DateTime and hoping it does the right thing when it detects an integer column doesn't work, it works for timestamp fields and date fields.

score 8 · Accepted Answer · answered May 15 '09 at 00:44

I think there is a couple of issues with the type decorator you showed.

impl should be sqlalchemy.types.Integer instead of DateTime.
The decorator should allow nullable columns.

Here's the what I have in mind:


import datetime, time
from sqlalchemy.types import TypeDecorator, DateTime, Integer

class IntegerDateTime(TypeDecorator):
    """a type that decorates DateTime, converts to unix time on
    the way in and to datetime.datetime objects on the way out."""
    impl = Integer # In schema, you want these datetimes to
                   # be stored as integers.
    def process_bind_param(self, value, _):
        """Assumes a datetime.datetime"""
        if value is None:
            return None # support nullability
        elif isinstance(value, datetime.datetime):
            return int(time.mktime(value.timetuple()))
        raise ValueError("Can operate only on datetime values. "
                         "Offending value type: {0}".format(type(value).__name__))
    def process_result_value(self, value, _):
        if value is not None: # support nullability
            return datetime.datetime.fromtimestamp(float(value))

Ok I like it, a shame this didn't make it to production in the end. But why did you replace engine with _? — rgz, May 30 '09 at 08:31
Just a habit. pylint dislikes unused variables, so by convention we use _. — Pavel Repin, May 30 '09 at 09:12

score 3 · Answer 2 · answered Apr 18 '09 at 09:19

So yeah, this approach works. And I ended up answering my own question :/, hope somebody finds this useful.

import datetime, time
from sqlalchemy.types import TypeDecorator, DateTime
class IntegerDateTime(TypeDecorator):
    """a type that decorates DateTime, converts to unix time on
    the way in and to datetime.datetime objects on the way out."""
    impl = DateTime
    def process_bind_param(self, value, engine):
        """Assumes a datetime.datetime"""
        assert isinstance(value, datetime.datetime)
        return int(time.mktime(value.timetuple()))
    def process_result_value(self, value, engine):
        return datetime.datetime.fromtimestamp(float(value))
    def copy(self):
        return IntegerDateTime(timezone=self.timezone)

Defining a table with sqlalchemy with a mysql unix timestamp

2 Answers2

Linked