func.now() should translate to calling SYSDATE for Oracle

[Former user]

Care should be taken that the return type is correct (date vs. datetime)

[Former user]

please add [e-mail] to cc for me

[Former user]

See also #1760

[Mike Bayer]

Can you give me some background/rationale for this ? is this another oracle 8 thing ?

from sqlalchemy import *
e = create_engine('oracle://scott:tiger@localhost/xe', echo='debug')

print e.execute("select sysdate from dual").scalar()
print e.execute("select current_timestamp from dual").scalar()

2011-02-03 12:10:57,608 INFO sqlalchemy.engine.base.Engine SELECT USER FROM DUAL
2011-02-03 12:10:57,608 INFO sqlalchemy.engine.base.Engine {}
2011-02-03 12:10:57,656 DEBUG sqlalchemy.engine.base.Engine Col ('USER',)
2011-02-03 12:10:57,699 DEBUG sqlalchemy.engine.base.Engine Row (u'SCOTT',)
2011-02-03 12:10:58,041 INFO sqlalchemy.engine.base.Engine select sysdate from dual
2011-02-03 12:10:58,041 INFO sqlalchemy.engine.base.Engine {}
2011-02-03 12:10:58,084 DEBUG sqlalchemy.engine.base.Engine Col ('SYSDATE',)
2011-02-03 12:10:58,151 DEBUG sqlalchemy.engine.base.Engine Row (datetime.datetime(2011, 2, 3, 17, 10, 44),)
2011-02-03 17:10:44
2011-02-03 12:10:58,209 INFO sqlalchemy.engine.base.Engine select current_timestamp from dual
2011-02-03 12:10:58,209 INFO sqlalchemy.engine.base.Engine {}
2011-02-03 12:10:58,252 DEBUG sqlalchemy.engine.base.Engine Col ('CURRENT_TIMESTAMP',)
2011-02-03 12:10:58,301 DEBUG sqlalchemy.engine.base.Engine Row (datetime.datetime(2011, 2, 3, 12, 10, 44, 898940),)
2011-02-03 12:10:44.898940

[Mike Bayer]

I'll note that now() resolves to CURRENT_TIMESTAMP currently.

classics-MacBook-Pro:sqlalchemy classic$ python
Python 2.7.0+ (release27-maint:85873, Oct 27 2010, 19:38:38) 
[4.0.1 (Apple Inc. build 5494)](GCC) on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from sqlalchemy import *
>>> e = create_engine('oracle://scott:tiger@localhost/xe', echo='debug')
>>> print e.scalar(func.now())
2011-02-03 12:12:52,505 INFO sqlalchemy.engine.base.Engine SELECT USER FROM DUAL
2011-02-03 12:12:52,506 INFO sqlalchemy.engine.base.Engine {}
2011-02-03 12:12:52,556 DEBUG sqlalchemy.engine.base.Engine Col ('USER',)
2011-02-03 12:12:52,600 DEBUG sqlalchemy.engine.base.Engine Row (u'SCOTT',)
2011-02-03 12:12:52,976 INFO sqlalchemy.engine.base.Engine SELECT CURRENT_TIMESTAMP AS now_1 FROM DUAL
2011-02-03 12:12:52,976 INFO sqlalchemy.engine.base.Engine {}
2011-02-03 12:12:53,019 DEBUG sqlalchemy.engine.base.Engine Col ('NOW_1',)
2011-02-03 12:12:53,062 DEBUG sqlalchemy.engine.base.Engine Row (datetime.datetime(2011, 2, 3, 12, 12, 39, 668940),)
2011-02-03 12:12:39.668940
>>>

[Former user]

Replying to zzzeek:

Can you give me some background/rationale for this ? is this another oracle 8 thing ?

Mainly, yes, CURRENT_DATE & CURRENT_TIMESTAMP didn't arrive until Oracle 9i.

But there is another subtle difference. SYSDATE's granularity is only to the second, while CURRENT_TIMESTAMP is much more granular. It's possible this is important depending upon context.

I've implemented this with myself, so it isn't a big factor for me, just thought it would be good for database independence. (I should like to be able to invoke a function such as now() and get my response regardless of database being postgres, oracle 9, oracle 8, etc.)

[Mike Bayer]

func.now() generally does return the microseconds on other backends. So SYSDATE would likely be only on oracle-8 check.