timestamp format in 12c

jl66
jl66 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
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
sdstuber
Most Valuable Expert 2011
Top Expert 2012
Commented:
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;
sdstuber
Most Valuable Expert 2011
Top Expert 2012
Commented:
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

jl66Consultant

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial