1. Michael Bayer
  2. sqlalchemy
  3. Issues

Issues

Issue #1758 new

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

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

Comments (9)

  1. Michael Bayer repo owner

    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
    
  2. Michael Bayer repo owner

    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
    >>>
    
  3. Anonymous

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

  4. Log in to comment