0

As Date uses a precision only up to seconds and we need to use timestamp to have more precision (upto milliseconds).

I run a query and sort in descending order of the create_date field which returns results in right order according to transaction execution.

3 results with different transaction codes (TRN, DII, DRD) in that order, all show same create_date values (e.g.: 28-MAY-13 12.09.45).

I am not able to see the milliseconds, but DRD is created milliseconds before DII which is created few milliseconds before TRN.

So it does capture the creation times upto milliseconds and displays accordingly in right order, but how can I see this time? I tried: 

to_timestamp(create_date, 'DD.MM.YYYY HH24:MI:SSFF3' )

but still shows 0 for milliseconds part.

Noel
  • 10,152
  • 30
  • 45
  • 67
pri_dev
  • 11,315
  • 15
  • 70
  • 122
  • Duplicate of http://stackoverflow.com/questions/2343708/oracle-fetch-date-time-in-milliseconds-from-date-datatype-field – Dave Michener May 28 '13 at 20:14
  • According to [Oracle 11.1 Docs](http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#autoId12) `For each DATE value, Oracle stores the following information: century, year, month, date, hour, minute, and second.` Notice that they did not list milliseconds. – Dave Michener May 28 '13 at 20:18
  • Apologies - Just realized that you are using TIMESTAMP data types. – Dave Michener May 28 '13 at 20:22
  • If the `create_date` column is a `TIMESTAMP` data type, then you should not need to use the `to_timestamp` function to convert that value. Running the following query on the Oracle Jobs Scheduler table which has a `TIMESTAMP(6)` column shows the milliseconds fine: `select last_start_date, to_char(last_start_date, 'DD.MM.YYYY HH24:MI:SS:FF3') from SYS.all_scheduler_jobs` – Dave Michener May 28 '13 at 20:53

1 Answers1

1

Your created_date field should be of TIMESTAMP type. When inserting into this column use systimestamp instead of sysdate to store the milliseconds.

Noel
  • 10,152
  • 30
  • 45
  • 67