Oracle and PL/SQL Recipes: A Problem-Solution Approach

7-11. Tracking Time to a Millisecond

Problem

You are interested in tracking time in a finely grained manner to the millisecond. For example, you want to determine the exact time in which a particular change is made to the database.

Solution

Perform simple mathematics with the current date time in order to determine the exact time down the millisecond. The following function accepts a timestamp and returns the |milliseconds:

CREATE OR REPLACE FUNCTION capture_milliseconds(in_time TIMESTAMP) RETURN NUMBER IS   milliseconds    NUMBER; BEGIN select sum(    (extract(hour from in_time))*3600+    (extract(minute from in_time))*60+    (extract(second from in_time)))*1000 into MILLISECONDS from dual; RETURN milliseconds; END;

How It Works

If your application ...