python - Convert datetime to unix timestamp in SQLAlchemy model before executing query?

Question

I am using SQLAlchemy to work with a remote database that uses a strange timestamp format--it stores timestamps as double-precision milliseconds since epoch. I'd like to work with python datetime objects, so I wrote getter/setter methods in my model, following this gist:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import synonym
from sqlalchemy.dialects.mysql import DOUBLE
import datetime

Base = declarative_base()
class Table(Base):
    __tablename__ = "table"

    id = Column(Integer, primary_key=True)
    _timestamp = Column("timestamp", DOUBLE(asdecimal=False))

    @property
    def timestamp(self):
        return datetime.datetime.utcfromtimestamp(float(self._timestamp)/1000.)

    @timestamp.setter
    def timestamp(self, dt):
        self._timestamp = float(dt.strftime("%s"))*1000.

    timestamp = synonym('_timestamp', descriptor=timestamp)

This works great for inserting new rows into the table and working with objects from the table:

>>> table = session.query(Table).first()
<Table id=1>
>>> table.timestamp
datetime.datetime(2016, 6, 27, 16, 9, 3, 320000)
>>> table._timestamp
1467043743320.0

However, it breaks down when I try to use a datetime in a filter expression:

>>> july = datetime.datetime(2016, 7, 1)
>>> old = session.query(Table).filter(Table.timestamp < july).first()
/lib/python2.7/site-packages/sqlalchemy/engine/default.py:450: Warning: Truncated incorrect DOUBLE value: '2016-07-01 00:00:00'
>>> july_flt = float(july.strftime("%s"))*1000.
>>> old = session.query(Table).filter(Table.timestamp < july_flt).first()
<Table id=1>

I assume this is because my getter/setter methods apply to instances of the table class, but don't change the behavior of the class itself. I've tried rewriting using a hybrid property instead of a synonym:

from sqlalchemy.ext.hybrid import hybrid_property

class Table(Base):
    __tablename__ = "table"

    id = Column(Integer, primary_key=True)
    _timestamp = Column("timestamp", DOUBLE(asdecimal=False))

    @hybrid_property
    def timestamp(self):
        return datetime.datetime.utcfromtimestamp(float(self._timestamp)/1000.)

    @timestamp.setter
    def timestamp(self, dt):
        self._timestamp = float(dt.strftime("%s"))*1000.

Again, this works with Table instances, but fails on a query--now it's hitting my getter method when I run the query:

>>> july = datetime.datetime(2016, 7, 1)
>>> old = session.query(Table).filter(Table.timestamp < july).first()
Traceback:
  File "models.py", line 42, in timestamp
    return datetime.datetime.utcfromtimestamp(float(self._timestamp)/1000.)
TypeError: float() argument must be a string or a number

With the debugger, I can see that the getter is receiving the Table._timestamp class (not a specific Table._timestamp, and not 'july').

I see that I could use the hybrid_property.expression decorator to define a SQL expression for converting timestamps into datetime, but what I'd really like is to convert the datetime into a timestamp on the python side, then run the query using timestamps. In other words, I'd like to use datetimes everywhere (including in queries), but have everything done with the microsecond timestamps on the SQL side. How can I do this?

Answer 1

You have to use a custom type, which isn't as scary as it sounds.

from sqlalchemy.types import TypeDecorator


class DoubleTimestamp(TypeDecorator):
    impl = DOUBLE

    def __init__(self):
        TypeDecorator.__init__(self, as_decimal=False)

    def process_bind_param(self, value, dialect):
        return value.replace(tzinfo=datetime.timezone.utc).timestamp() * 1000

    def process_result_value(self, value, dialect):
        return datetime.datetime.utcfromtimestamp(value / 1000)

Then Table becomes:

class Table(Base):
    __tablename__ = "table"

    id = Column(Integer, primary_key=True)
    timestamp = Column(DoubleTimestamp)

And then everything you mention works. You insert, select and compare with datetimes but it's stored as a DOUBLE.

Here I've used different logic for converting between timestamps since strftime('%s') isn't the correct solution. It's a different question which has been answered correctly here. Oh and I noticed you said microseconds but only convert to milliseconds in the code you posted, unless it was a slip of the tongue ??.