SQL: date datatypes & milliseconds (aka how to use timestamps instead of sequences)

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

Another userful feature of the DATE type is the easy language conversion done by the TO_CHAR function:

SELECT TO_CHAR(SYSDATE, 'FMDD Month YYYY', 'NLS_DATE_LANGUAGE=italian') FROM DUAL

or in a more fashionable way:

WITH     target_languages     AS

(

 SELECT     'German' AS language FROM dual  UNION ALL

 SELECT     'French'             FROM dual  UNION ALL

 SELECT     'Italian'            FROM dual 

)

SELECT language

      ,TO_CHAR ( SYSDATE,'FMDD Month YYYY','NLS_DATE_LANGUAGE=' || language) AS t_c

  FROM target_languages

Nice tricks Mack, nice tricks. but... why have you used TIMESTAMP?

In my case i needed to track down the user insert/updates and i chose to add a column that simulated an oracle sequence without using a sequence:

TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF6')

as the default value of the new column. try it yourself.
It can be used instead of a sequence, you can order the column and you can find out date and time of the insert.