I'm interested in being able to use second time intervals on PostgreSQL, Oracle 8 *and *modern Oracle versions, agnostically.
The native python timedelta works great for the postgres and cx_Oracle drivers. However *cx_Oracle connected to Oracle 8 won't support this* so I'm building my own construct to help. We want support for something like this: *current_timestamp() + timedelta(seconds=15)* (Assume "current_timestamp()" works on all these databases -- I've already built that.) *PostgreSQL*, works great with python's timedelta: >>> DBSession.execute(select([current_timestamp() + timedelta(seconds=15)])).scalar() MainThread: 09:15:20,871 INFO [sqlalchemy.engine.base.Engine] BEGIN (implicit) MainThread: 09:15:20,872 INFO [sqlalchemy.engine.base.Engine] SELECT localtimestamp + %(param_1)s AS anon_1 MainThread: 09:15:20,872 INFO [sqlalchemy.engine.base.Engine] {'param_1': datetime.timedelta(0, 15)} datetime.datetime(2016, 5, 28, 9, 15, 35, 872999) *Modern Oracle*, works great with python's timedelta: >>> DBSession.execute(select([current_timestamp() + timedelta(seconds=15)])).scalar() MainThread: 09:28:15,009 INFO [sqlalchemy.engine.base.Engine] BEGIN (implicit) MainThread: 09:28:15,010 INFO [sqlalchemy.engine.base.Engine] SELECT systimestamp + :param_1 AS anon_1 FROM DUAL MainThread: 09:28:15,010 INFO [sqlalchemy.engine.base.Engine] {'param_1': datetime.timedelta(0, 15)} datetime.datetime(2016, 5, 28, 9, 28, 30, 11530) *Oracle 8: *no >>> DBSession.execute(select([current_timestamp() + timedelta(seconds=15)])).scalar() MainThread: 08:29:37,659 INFO [sqlalchemy.engine.base.Engine] SELECT sysdate + :param_1 AS anon_1 FROM DUAL MainThread: 08:29:37,659 INFO [sqlalchemy.engine.base.Engine] {'param_1': datetime.datetime(1970, 1, 1, 0, 0, 15)} Traceback (most recent call last): File "<console>", line 1, in <module> File "/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/orm/scoping.py", line 114, in do return getattr(self.registry(), name)(*args, **kwargs) File "/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py", line 804, in execute clause, params or {}) File "/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1450, in execute params) File "/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1583, in _execute_clauseelement compiled_sql, distilled_params File "/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1697, in _execute_context context) File "/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1690, in _execute_context context) File "/home/train/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.7.607kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py", line 335, in do_execute cursor.execute(statement, parameters) DatabaseError: (DatabaseError) ORA-00975: date + date not allowed 'SELECT sysdate + :param_1 AS anon_1 FROM DUAL' {'param_1': datetime.datetime(1970, 1, 1, 0, 0, 15)} I've already constructed a class called "seconds_interval" that works for Oracle 8, but I don't know how to use the default behavior when not Oracle 8: *Oracle 8 usage:* >>> DBSession.execute(select([current_timestamp() + *seconds_interval(15)* ])).scalar() MainThread: 08:37:06,539 INFO [sqlalchemy.engine.base.Engine] SELECT sysdate +* :seconds_1/86400 *AS anon_1 FROM DUAL MainThread: 08:37:06,539 INFO [sqlalchemy.engine.base.Engine] {'seconds_1': 15} datetime.datetime(2016, 5, 28, 8, 37, 25) class seconds_interval(FunctionElement): type = Interval() name = 'seconds' def __init__(self, *args, **kwargs): FunctionElement.__init__(self, *args, **kwargs) self.seconds = args[0] @compiles(seconds_interval) def compile_seconds_interval(element, compiler, **kw): if compiler.dialect.name == 'oracle' and \ compiler.dialect.server_version_info < (9,): return "%s/86400" % compiler.process(element.clauses) else: # use timedelta as bind param from datetime import timedelta td = timedelta(seconds=element.seconds) *return ...???* Can anyone help me with the else: above to use the native python timedelta as a bind param? (Or trash it completely if there is a better strategy?) Much thanks in advance! Kent -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.