Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Time fraction(6) with Oracle 9i on Windows NT

Re: Time fraction(6) with Oracle 9i on Windows NT

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 17 Sep 2004 05:24:23 +1000
Message-Id: <4149e864$0$4310$afc38c87@news.optusnet.com.au>


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



COL2

17/SEP/04 05:17:09.00 AM
17/SEP/04 05:17:09.0000 AM I confess right up front to not having an NT machine to test on (and I wouldn't want one, even if one were offered!). So that example comes from Windows 2000. But it *ought* to be the same for NT... point is, if you want real help here you'll have to do the testing for us, and show us in detail what is actually going on here.

Regards
HJR Received on Thu Sep 16 2004 - 14:24:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US