about to store millseconds ..

Hi Friends,

I want to Store in mydatabase time with millseconds. Is it Possible to Store this in Oracle? Upto My knowledge i know that the Date Datatype will store Hours,Minutes and seconds.

Sekhar

U R right? I think you have to use JAVA for getting the millsec, if you want, and store in separate field.

You can use DBMS_UTILITY.GET_TIME but have in mind that it is not syncronized with SYSDATE. The time you get (a number) is the number of 100th's of a second from some arbitrary epoch.

In 9i, there are new time formats, one of them is TIMESTAMP it shows the miliseconds even with precision of 9, default is 6.

Code:

---


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production

SQL> alter session set events '10406 trace name context forever';

Session altered.

SQL> create table a (kk timestamp); 

Table created.

SQL> insert into a values(sysdate);

1 row created.

SQL> select * from a;

KK
---------------------------------------------------------------------------
17-JAN-02 11.38.54 AM

---

cool huh :D

Quote:

---

cool huh

---

Yep :-)

Even this is possible;

insert into a values(systimestamp);

But look at select * from a;




[Edited by julian on 01-17-2002 at 07:02 AM]

Hi,


Can anyone please elaborate on set events '10406 trace name context forever' or tell me where can I get more info on this.


Thanks and Regards,
Vijay R.

10406 is used to enable timestamp dtaatype in Oracle 8i, of course it's not supported officially

True, this event enables you to store timestamp dataype values and use timestamp related functions inside 8i, but don't be fooled by this semi-feature! For example, it alows you to use SYSTIMESTAMP as Julian has pointed out, but make some tests with SYSTIMESTAMP inserts and see what fractional seconds are realy stored! Definitely not something that has anything to do with miliseconds or system clock! :(

000000000

:(

cool

I altered my system with this set Command.. Now Can I unset my system ...

Quote:

---

Originally posted by jmodic
True, this event enables you to store timestamp dataype values and use timestamp related functions inside 8i, but don't be fooled by this semi-feature! For example, it alows you to use SYSTIMESTAMP as Julian has pointed out, but make some tests with SYSTIMESTAMP inserts and see what fractional seconds are realy stored! Definitely not something that has anything to do with miliseconds or system clock! :(

---

Actually you haven't altered your system, you have altered your session. Remeber the command being "alter session set events ..."? So this event is set only inside your session and only for the duration of your session. As soon as you close your session the event is not in effect any more.

Thats ok..As i need to handle the same for multiple sessions i used the same command for alter system. Its working fine and as i am using this feature in order by clause... thats solving my problem.. But now i want unset this to my system..??

Quote:

---

Originally posted by jmodic
Actually you haven't altered your system, you have altered your session. Remeber the command being "alter session set events ..."? So this event is set only inside your session and only for the duration of your session. As soon as you close your session the event is not in effect any more.

---