title: SQLAlchemy - Convert sqlalchemy.sql.functions.now() to Unix epoch (with fraction)
body: <p>I'm using Doubles to track time in my database (as Unix timestamps with fractional portions). For that, I've created the following user type (that takes pytz-aware datetimes from the application and converts them to UTC timestamps). </p>
  
  <pre><code>from sqlalchemy.types import TypeDecorator, Float
  
  class DoubleDatetime(TypeDecorator):
  
      impl = Float
  
      def __init__(self):
          TypeDecorator.__init__(self, as_decimal=False)
  
      def process_bind_param(self, value, dialect):
          return value.timestamp()
  
      def process_result_value(self, value, dialect):
          return datetime.datetime.utcfromtimestamp(value)
  </code></pre>
  
  <p>This works fine for all datetimes in my system. I'm using the following column definition to have the database (in my case, SQLite3) insert the transaction time:</p>
  
  <pre><code>from sqlalchemy import Column
  from sqlalchemy.sql.functions import now
  
  column = Column("transaction_begin", DoubleDatetime, server_default=now())
  </code></pre>
  
  <p>Despite defining the <code>transaction_begin</code> column as a <code>DoubleDatetime</code>, my database still stores the records with a string.</p>
  
  <p>How can I force the server transaction timestamp to use fractional Unix epochs?</p>
  
  <p><strong>What have I tried?</strong></p>
  
  <p>Reading the SQLite3 docs, I know I need to do some Julian Day trickery to do this.</p>
  
  <pre><code>SELECT (julianday('now') - 2440587.5) * 86400.0;
  </code></pre>
  
  <p>But I can't find julianday in <code>sqlalchemy.sql.functions</code>, to wrap it in a zero-argument lambda (to pass as the <code>server_default</code> arg to <code>Column</code>).</p>
  
  <p>I tried the following, but it doesn't seem to work.</p>
  
  <pre><code>from sqlalchemy import text    
  server_default = text("SELECT (julianday('now') - 2440587.5) * 86400.0;")
  </code></pre>
  
  <p>Nor does the following:</p>
  
  <pre><code>from sqlalchemy.sql import select, text
  server_default = select([text("(julianday('now') - 2440587.5) * 86400.0;")])
  </code></pre>
  
  <p>Traceback:</p>
  
  <pre><code>Traceback (most recent call last):
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
      context)
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
      cursor.execute(statement, parameters)
  sqlite3.OperationalError: near "SELECT": syntax error
  
  The above exception was the direct cause of the following exception:
  
  Traceback (most recent call last):
    File "tasks.py", line 81, in &lt;module&gt;
      metadata.create_all(engine)
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/schema.py", line 4005, in create_all
      tables=tables)
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1940, in _run_visitor
      conn._run_visitor(visitorcallable, element, **kwargs)
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1549, in _run_visitor
      **kwargs).traverse_single(element)
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
      return meth(obj, **kw)
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py", line 757, in visit_metadata
      _is_metadata_operation=True)
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
      return meth(obj, **kw)
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py", line 791, in visit_table
      include_foreign_key_constraints=include_foreign_key_constraints
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
      return meth(self, multiparams, params)
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
      return connection._execute_ddl(self, multiparams, params)
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1009, in _execute_ddl
      compiled
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
      context)
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
      exc_info
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
      reraise(type(exception), exception, tb=exc_tb, cause=cause)
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
      raise value.with_traceback(tb)
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
      context)
    File "/home/randm/Libraries/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
      cursor.execute(statement, parameters)
  sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "SELECT": syntax error [SQL: "\nCREATE TABLE barchartbarmessage (\n\tsymbol VARCHAR NOT NULL, \n\tdate DATE NOT NULL, \n\topen NUMERIC, \n\thigh NUMERIC, \n\tlow NUMERIC, \n\tclose NUMERIC, \n\tvolume INTEGER, \n\tprevious_day_open_interest INTEGER, \n\tprevious_day_volume INTEGER, \n\tmessage_time FLOAT, \n\ttransaction_begin FLOAT DEFAULT SELECT (julianday('now') - 2440587.5) * 86400.0; NOT NULL, \n\ttransaction_end FLOAT, \n\tPRIMARY KEY (symbol, date, transaction_begin)\n)\n\n"] (Background on this error at: http://sqlalche.me/e/e3q8)
  </code></pre>