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.

Case statement in SQL and PL/SQL

The following as examples using SIMPLE and SEARCHED CASE statement in pl/sql.
Simple CASE:

text := case n
when 1 then one
when 2 then two

when 3 then three
else other
end case;


Searched CASE:


text := case
when n = 1 then one

when n = 2 then two
when n = 3 then three
when ( n > 3 and n <>
else other
end;


Exception handling:

...
case
when p = 1 then Action1;
when r = 2 then Action2;
when q > 1 then Action3;
end case;
exception
when case_not_found
...



In SQL, you can also have SIMPLE and SEARCHED case.

SELECT last_name, commission_pct,
(CASE commission_pct
WHEN 0.1 THEN ‘Low’
WHEN 0.15 THEN ‘Average’
WHEN 0.2 THEN ‘High’
ELSE ‘N/A’
END ) Commission
FROM employees ORDER BY last_name;



SELECT last_name, job_id, salary,
(CASE
WHEN job_id LIKE 'SA_MAN' AND salary <>
WHEN job_id LIKE 'SA_MAN' AND salary >= 12000 THEN '15%'
WHEN job_id LIKE 'IT_PROG' AND salary <>
WHEN job_id LIKE 'IT_PROG' AND salary >= 9000 THEN '12%'
ELSE 'NOT APPLICABLE'
END ) Raise
FROM employees;