timestamp format in 12c

We have a query below:

select to_char(EVENT_TIMESTAMP,'MM/DD/YYYY HH24:MI:SS.FFFFFF') Timestamp from unified_audit_trail;

DATE
--------------------------------------
01/25/2015 10:19:29.725385325345625375
02/27/2015 11:17:17.034196027191037156

My question is what was got is not what was wanted. I expected 01/25/2015 10:19:29.725385 for 1st record, but it gave me 01/25/2015 10:19:29.725385325345625375.
1. How can I get what I want?
2. Does the many digits after decimal point mean a high precision of fraction of a second?

Please can any gurus shed some light on it?

It might help to see what I mean by putting some delimiters between the repeated FF format

Here are the first 10 rows from one of my 12.1.0.2 dbs

Note how the fractional part of the time is 3 chunks of repeated values.
Each chunk resulting from one instance of FF

Just like if you specify MMMMMM you'll get the 2-digit month repeated 3 times, not a single 6-digit month

SELECT TO_CHAR(event_timestamp, 'MM/DD/YYYY HH24:MI:SS.FFFFFF') timestamp,
       TO_CHAR(event_timestamp, 'MM/DD/YYYY HH24:MI:SS.FF-FF-FF') timestamp2
  FROM unified_audit_trail;

08/07/2014 11:30:46.493384493384493384	08/07/2014 11:30:46.493384-493384-493384
08/07/2014 11:31:05.967303967303967303	08/07/2014 11:31:05.967303-967303-967303
08/28/2014 13:04:06.189907189907189907	08/28/2014 13:04:06.189907-189907-189907
08/28/2014 13:06:42.792140792140792140	08/28/2014 13:06:42.792140-792140-792140
08/28/2014 13:07:46.869108869108869108	08/28/2014 13:07:46.869108-869108-869108
09/04/2014 14:25:27.933324933324933324	09/04/2014 14:25:27.933324-933324-933324
09/04/2014 14:25:51.528715528715528715	09/04/2014 14:25:51.528715-528715-528715
09/04/2014 14:26:47.286859286859286859	09/04/2014 14:26:47.286859-286859-286859
09/04/2014 14:44:53.964004964004964004	09/04/2014 14:44:53.964004-964004-964004
09/08/2014 08:56:42.876052876052876052	09/08/2014 08:56:42.876052-876052-876052

Open in new window

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 82

slightwv (䄆 Netminder) Commented: 2016-01-27

Sort of.

If you want 6 numbers for fractional seconds try:
select to_char(EVENT_TIMESTAMP,'MM/DD/YYYY HH24:MI:SS.FF6') Timestamp from unified_audit_trail;

The allowed options are in the docs:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF00212

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 75

sdstuberCommented: 2016-01-27

I'm curious how you got the results you did. It doesn't seem possible that your sql could have produced the results you posted.

nonrepeating patterns of 18 decimal points of precision doesn't seem normal.
According to oracle documentation, the maximum precision supported is 9 digits with default being 6.

With your query, I would expect results like this: (6 digits of precision from the FF, but repeated 3 times because of FFFFFF)

01/25/2015 10:19:29.725385725385725385
02/27/2015 11:17:17.034196034196034196

What is your platform?

Can you dump those values?

select dump(EVENT_TIMESTAMP) from unified_audit_trail;

jl66ConsultantAuthor Commented: 2016-01-28

Outstanding! I wish I could give each of you 1000points.

timestamp format in 12c

Who is Participating?