Oracle: Select Milliseconds from Timestamp Type that Defaults with SYSTIMESTAMP

Question

I have a column with TIMESTAMP type in my Oracle Database. The default column value is SYSTIMESTAMP.

I want to SELECT milliseconds FROM the TIMESTAMP column. I use the query below without success:

SELECT TO_CHAR (MY_TIMESTAMP, 'dd-mm-yyyy hh24:mi:ss.FF') AS MY_TIMESTAMP
FROM MY_TABLE

-- Result: 20-12-2015 15:23:28.

As you see the result does not have any milliseconds and it is empty. If I change the query with .FF4 then it results .0000.

How can I SELECT the column with milliseconds?

Answer 1

The precision for timestamp columns can be set up by

TIMESTAMP [(fractional_seconds_precision)].

In your case for 4 it's:

create table my_table
(id number,
  my_TIMESTAMP timestamp(4) default SYSTIMESTAMP);

You can check your current precision by:

select column_name, data_scale from user_tab_columns 
where table_name = 'MY_TABLE' and column_name = 'MY_TIMESTAMP';

Here is sample in SQL Fiddle

The display can be change by:

 alter session set NLS_TIMESTAMP_FORMAT = 'DD-MON-RRRR HH:MI:SS.FF9';