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

Hello Howard,

Please, find my comments below.

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:4149e864$0$4310$afc38c87_at_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 used following create statements:

create table timetestA ( col1 timestamp, PRIMARY KEY (col1));

create table timetestB ( col1 timestamp(6), PRIMARY KEY (col1));

create table timetestC ( col1 timestamp(12), PRIMARY KEY (col1));

I used following insert statements:

insert into timetestB (col1) values (CURRENT_TIMESTAMP);

insert into timetestB (col1) values (systimestamp);

insert into timetestB (col1) values (CURRENT_TIMESTAMP(6));

insert into timetestB (col1) values (CURRENT_TIMESTAMP(9));

insert into timetestB (col1) values (systimestamp(6));

insert into timetestB (col1) values (systimestamp(9));

In all the cases results are the same!

17-SEP-04 11.12.41.861000 AM
17-SEP-04 11.32.22.185000 AM
17-SEP-04 11.32.41.982000 AM
17-SEP-04 11.33.10.591000 AM
17-SEP-04 11.37.15.696000 AM
17-SEP-04 11.37.31.164000 AM
17-SEP-04 11.37.51.258000 AM

Actually, I am not an Oracle guy (Before I worked with DB2, NonStop SQL, Sybase),

but I think the test above are logical ones, and they show that

our current installation of Oracle 9i does not support correct

fraction (6) generation.

Do you have any further ideas?

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

Actually, we use Microsoft Windows Server 2003,

but it make no difference, cause all Windows platforms

have the same problem with timestamps!

P.S. I heard that there are some extensions for Oracle. Usually they referred

as cartridges.

>
> Regards
> HJR
>

With best wishes,

Eugene Petrov. Received on Fri Sep 17 2004 - 03:57:53 CDT