Monday, September 04, 2006

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;

6 comments:

Anonymous said...

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

Jason said...

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>

Aditya said...

Thanks for the info... not many sites refer to systimestamp

burzaco said...

My friend, its exellent topic ! see you on the road !

Anonymous said...

So simple information, so hard to find. Excellent post!! Thanks you, Pablo from Argentina.

buyi wen said...

I find another free online Unix timestamp converter, it can procide the current time stamp.