Precision of a Timestamp in Oracle

Sometimes in Oracle you could need to explicitly specify the precision of a TIMESTAMP field. In case, you should use the "DUAL" pattern.

Let' s give an example:

SELECT to_char(CURRENT_TIMESTAMP, 'YYYYMMDD HH24MISS.FFN') FROM DUAL;

where "N" refers to the desired precision (number of digits after the comma). We use an explicit cast using the "TO_CHAR" function, since we wanna see the real output - and not the one setted in the IDE environment (TOAD, SQLPLUS, or SQL Developer).

So, for istance:

SELECT to_chat(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL;

will result in something like:

20120326 114347.215

and

SELECT to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF9') FROM DUAL;

in:

20120326 114551.183876000