Oracle TIMESTAMP with sub-second resolution

[Former user]

From the mailing list...

On Sep 17, 2006, at 5:30 PM, Lee, Bryson wrote:

I'm implementing an application against an Oracle 10g database in which I need to use TIMESTAMP columns with sub-second resolution.
The cx_Oracle driver requires one to use the setinputsizes() cursor method to assign a type of cx_Oracle.TIMESTAMP to datetime.datetime bind parameters in order to capture the fractional seconds.

Michael Bayer [e-mail] responded:

no, we'd have to build a hook of some kind into SA's oracle dialect when bind parameters are processed for execution, as well as some kind of flag on the DateTime type to indicate this.

if you can add a trac ticket for this, that would be helpful.

[Former user]

prototype implementation

[Former user]

I've uploaded a possible implementation that appears to work for some simple cases.

[Mike Bayer]

ok this is pretty good, but im going to build the feature into the pre_exec feature of ExecutionContext and have it go off of the type objects that are assembled into the sql.ClauseParameters object. the other thing needed is a test case which I will add as an oracle-only test in the test/sql/testtypes.py test suite.

bumping this up to remind me to get to this next.

[Mike Bayer]

changeset:1944 contains the version of this code which i want. however, I cant get it to work, at least i cant tell if its working. i added a unit test which tests sub-second resolution for Oracle and the data goes in, but I do not get the sub-second values back. do i have to call setoutputsizes to get the data back properly ?

the oracle dialect now gets this flag:

e = create_engine('oracle://url', auto_setinputsizes=True)

which will call setinputsizes() for all executions.

I also can find no documentation whatsoever on cx_oracle's ability to do timestamps, or any mention that setinputsizes is required. cx_oracle also seems to have no mailing list of any kind which is pretty, er, well.

do you have some documentation illustrating a full round-trip of subsecond timestamps ? otherwise i cant really close this issue.

[Former user]

I've tried this with the 3.0 release, and it appears to work in some cases and not others:

• For auto-loaded Table objects, sub-second timestamps roundtrip OK, e.g.: import datetime import sqlalchemy as sql md = BoundMetaData( 'oracle://...', auto_setinputsizes=True ) tbl = sql.Table( 'ts_test', md, autoload=True ) t1 = datetime.datetime.utcnow() tbl.insert().execute( ts=t1 ) t2, = tbl.select().execute().fetchone() t2 == t1 True

• For non-autoloaded tables, however, no fractional seconds get stored or returned. If you replace the autoloaded table in the above example with tbl = sql.Table( 'ts_test_noauto', md, sql.Column( ts, sql.TIMESTAMP ) ) tbl.create() and insert / select the same value, the resulting datetime object has no microseconds.

• For non-compiled insert statements performed directly with engine.execute(), the fractional seconds aren't stored -- in fact, the pre_exec method of the ExecutionContext object isn't even called.

Since the case of autoloaded table metadata appears to work, that's good enough for me for now. Having the schema definition in an external DDL file is OK, as that's actually a bit easier for some of my colleagues to review.

Thanks for including this feature!

[Mike Bayer]

recent fixes in changeset:2740 and changeset:2742 got this to work.