SQL: date datatypes & milliseconds

This morning I was playing with dates while executing an anonymous plslq block from a java class. My goal was to trace the execution time of a class... including milliseconds.

The well known SYSDATE use the "DATE" datatype but from oracle 9i we can use SYSTIMESTAMP (using "TIMESTAMP" datatype):

SELECT
to_char(sysdate, 'YYYY:MM:DD:HH24:MI:SS')
,to_char(systimestamp, 'YYYY:MM:DD:HH24:MI:SS.FF6')
FROM dual

when using TIMESTAMP the comparison can be made through TO_TIMESTAMP (or TO_TIMESTAMP_TZ if having more than one zone field, TZ_OFFSET for getting the region time delay)

so... why we still have to use sysdate?
Because we can configure what is returned from the SYSDATE function modifing the "fixed_date" init parameter; I found it very usefull when testing application that are date-related.

ALTER SYSTEM SET FIXED_DATE = '2011-01-01-00:00:01';
System altered.

select sysdate from dual;

SYSDATE
-----------
01-JAN-2011


select systimestamp from dual;

SYSTIMESTAMP
------------------------------
08-JUL-2011 11.05.02.298000 AM