Oracle and getting Milliseconds from Sysdate... [Systimestamp]
First, you can't.
Sysdate doesn't support going to milliseconds. However, starting with Oracle 9, you can getting milliseconds from another system source : SYSTIMESTAMP.SELECT to_char(sysdate, 'HH24:MI:SS'), to_char(systimestamp, 'HH24:MI:SS.FF6') FROM dual;
Posts
6 comments:
Thanks for this pretty useful piece of information,
I was stuck on this for some milliseconds ;)
But why is systimestamp so slow compared to sysdate.
SQL> select systimestamp from dual;
SYSTIMESTAMP
-----------------------------------------------------------
28-MAR-08 03.26.33.574192 PM +01:00
Elapsed: 00:00:06.00
SQL> select sysdate from dual;
SYSDATE
-----------
28-MAR-2008
Elapsed: 00:00:00.01
Which version of Oracle are you using?
I don't know exactly how Oracle coded SYSDATE vs. SYSTIMESTAMP. I expect there is just more overhead with the OS level call aspect of SYSTIMESTAMP.
I will also tell you one more "trick". Whichever one is first, will be slow. Flip you test, and check your results.
Run this...
set echo on;
set timing on;
clear;
select sysdate from dual;
select systimestamp from dual;
select sysdate from dual;
select systimestamp from dual;
select sysdate from dual;
select systimestamp from dual;
select sysdate from dual;
select systimestamp from dual;
Results:
Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0
Connected as jvogel
select sysdate from dual;
SYSDATE
-----------
3/28/2008 1
Executed in 0.11 seconds
select systimestamp from dual;
SYSTIMESTAMP
-------------------------------------------------
28-MAR-08 11.41.31.853195 AM -05:00
Executed in 0.02 seconds
select sysdate from dual;
SYSDATE
-----------
3/28/2008 1
Executed in 0.02 seconds
select systimestamp from dual;
SYSTIMESTAMP
-------------------------------------------------
28-MAR-08 11.41.32.025039 AM -05:00
Executed in 0.02 seconds
select sysdate from dual;
SYSDATE
-----------
3/28/2008 1
Executed in 0.031 seconds
select systimestamp from dual;
SYSTIMESTAMP
-------------------------------------------------
28-MAR-08 11.41.32.210267 AM -05:00
Executed in 0.02 seconds
select sysdate from dual;
SYSDATE
-----------
3/28/2008 1
Executed in 0.03 seconds
select systimestamp from dual;
SYSTIMESTAMP
-------------------------------------------------
28-MAR-08 11.41.32.399820 AM -05:00
Executed in 0.04 seconds
SQL>
Thanks for the info... not many sites refer to systimestamp
My friend, its exellent topic ! see you on the road !
So simple information, so hard to find. Excellent post!! Thanks you, Pablo from Argentina.
I find another free online Unix timestamp converter, it can procide the current time stamp.
Post a Comment