Defining a table with sqlalchemy using timestamp mysql unix

Question

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

• Like a string, for example. "09/09/2009".
• As a whole, using the UNIX_TIMESTAMP () function, this is supposedly the traditional temporary unix representation (you know the seconds since the plus / minus jumps of seconds).
• Like MySQL TIMESTAMP, the particular mysql data type does not match unix timestamps.

• The mysql data type is used as the MySQL Date field.

  It is 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 such a way that I do not need to access the mysql function "FROM_UNIXTIME"?

  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.

Answer 1

I think there are a couple of problems with the creator of the style you showed.

• implshould be sqlalchemy.types.Integerinstead DateTime.
• The decorator must allow nullable columns.

Here is what I mean:

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))

Answer 2

So this approach works. And I answered my question: /, I hope someone 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)