title: Convert datetime to unix timestamp in SQLAlchemy model before executing query?
body: <p>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 <a href="https://gist.github.com/luhn/4170996" rel="noreferrer">this gist</a>:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>This works great for inserting new rows into the table and working with objects from the table:</p>
  
  <pre><code>&gt;&gt;&gt; table = session.query(Table).first()
  &lt;Table id=1&gt;
  &gt;&gt;&gt; table.timestamp
  datetime.datetime(2016, 6, 27, 16, 9, 3, 320000)
  &gt;&gt;&gt; table._timestamp
  1467043743320.0
  </code></pre>
  
  <p>However, it breaks down when I try to use a datetime in a filter expression:</p>
  
  <pre><code>&gt;&gt;&gt; july = datetime.datetime(2016, 7, 1)
  &gt;&gt;&gt; old = session.query(Table).filter(Table.timestamp &lt; july).first()
  /lib/python2.7/site-packages/sqlalchemy/engine/default.py:450: Warning: Truncated incorrect DOUBLE value: '2016-07-01 00:00:00'
  &gt;&gt;&gt; july_flt = float(july.strftime("%s"))*1000.
  &gt;&gt;&gt; old = session.query(Table).filter(Table.timestamp &lt; july_flt).first()
  &lt;Table id=1&gt;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>Again, this works with Table instances, but fails on a query--now it's hitting my getter method when I run the query:</p>
  
  <pre><code>&gt;&gt;&gt; july = datetime.datetime(2016, 7, 1)
  &gt;&gt;&gt; old = session.query(Table).filter(Table.timestamp &lt; 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
  </code></pre>
  
  <p>With the debugger, I can see that the getter is receiving the Table._timestamp class (not a specific Table._timestamp, and not 'july').</p>
  
  <p>I see that I could use the <a href="http://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html" rel="noreferrer">hybrid_property.expression</a> 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?</p>