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.

Pavel Repin · Accepted Answer · 2009-05-15 00:44:41Z

8

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

answered May 15, 2009 at 0:44

community wiki

Pavel Repin

Sign up to request clarification or add additional context in comments.

2 Comments

rgz Over a year ago

Ok I like it, a shame this didn't make it to production in the end. But why did you replace engine with _?

Pavel Repin Over a year ago

Just a habit. pylint dislikes unused variables, so by convention we use _.

rgz · Accepted Answer · 2009-04-18 09:19:18Z

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)

Collectives™ on Stack Overflow

Defining a table with sqlalchemy with a mysql unix timestamp

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related