2

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

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)

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:

from sqlalchemy import Column
from sqlalchemy.sql.functions import now

column = Column("transaction_begin", DoubleDatetime, server_default=now())

Despite defining the transaction_begin column as a DoubleDatetime, my database still stores the records with a string.

How can I force the server transaction timestamp to use fractional Unix epochs?

What have I tried?

Reading the SQLite3 docs, I know I need to do some Julian Day trickery to do this.

SELECT (julianday('now') - 2440587.5) * 86400.0;

But I can't find julianday in sqlalchemy.sql.functions, to wrap it in a zero-argument lambda (to pass as the server_default arg to Column).

I tried the following, but it doesn't seem to work.

from sqlalchemy import text    
server_default = text("SELECT (julianday('now') - 2440587.5) * 86400.0;")

Nor does the following:

from sqlalchemy.sql import select, text
server_default = select([text("(julianday('now') - 2440587.5) * 86400.0;")])

Traceback:

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 <module>
    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)
MikeRand
  • 4,788
  • 9
  • 41
  • 70

1 Answers1

1

I had this same question, and found that this worked. The expression here is specific to PostgreSQL. I originally was calculating this value on my application server, which led to unexpected results. See this post for more information. Note that in PostgreSQL, at least, when setting defaults using expressions you don't include SELECT at the start.

import sqlalchemy as sa

logged_ts_ms = sa.Column(sa.BigInteger, server_default=sa.text("""(extract(epoch from now()) * 1000)::bigint;"""))
madmatevz
  • 11
  • 2