Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Time fraction(6) with Oracle 9i on Windows NT
Eugene Petrov wrote:
> Can Oracle 9i generate microseconds for timestamp fields
> on Windows NT?
>
> I have a table with one timestamp field.
> Several inserts of CURRENT time gave following result:
> 2004-09-15-16.47.11.154000
> 2004-09-15-16.47.11.186000
> 2004-09-15-16.47.11.201000
> 2004-09-15-16.47.11.232000
>
> It looks like Oracle can only generate 3 fractional digits.
> We used the default options while installing Oracle 9i.
>
> May be there is some options that can affect this restriction?
> Some other RDBMS can generate the fraction(6).
> For example, DB2 would generate following:
> 2004-09-15-16.47.11.154001
> 2004-09-15-16.47.11.186002
> 2004-09-15-16.47.11.201003
> 2004-09-15-16.47.11.232004
>
> It appears that DB2 uses a static counter for generations microseconds.
> It is a normal approach with Windows (that has problems with fractions
> more then 3 digits!).
>
> Theoretically, We could generate fraction(6) manually and insert them in
> DB, but the system we migrate on Oracle is too big. We cannot replace all
> 'insert CURRENT' with the code generating correct timestamp fraction(6)!
>
> Who solved such problems, please, share your knowledge!
>
> Thanks in advance,
> Eugene Petrov.
It would help to see *how* you inserted the 'current time', rather than just seeing the results as you have them here. It would also help to see the table definition/creation statement, since the degree of decimal precision can be artificially restricted by the way the table is created.
For example:
SQL> create table timetestB (
2 col1 timestamp(2),
3 col2 timestamp(4));
Table created.
SQL> insert into timetestB values (sysdate, sysdate); 1 row created.
SQL> select * from timetestB;
COL1
Regards
HJR
Received on Thu Sep 16 2004 - 14:24:23 CDT