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?

What is the exact data type of the column? For TIMESTAMP you can specify the precision — Wernfried Domscheit, Dec 20 '15 at 12:54
@WernfriedDomscheit It is TIMESTAMP. Where can I specify the precision? — Vahid, Dec 20 '15 at 13:27
Please run this query and apend it's result to the question: `select column_name, data_type, data_length, data_precision, data_scale from user_tab_cols where table_name = 'MY_TABLE' and column_name = 'MY_TIMESTAMP'`. The query gives information about datatype and fractional seconds precision of the column in the table. Could be that the column has too short precision and doesn't store fractional seconds. — krokodilko, Dec 20 '15 at 13:51

score 1 · Accepted Answer · answered Dec 20 '15 at 14:06

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';

1 Answers1