Apex: Calculating the difference between a timestamp and the current time in an interactive report compute field.

 Requirement

Your user wants to express the difference between a timestamp column and the current time in a HH24:MI:SS format.

Solution

The easy way, if the user is happy to have the difference expressed in a readable way, is to simply apply the SINCE format to the column directly.

 

However, if the user wants the HH24:MI:SS, then the following formula can be used:

substr(current_timestamp - a, 1, 10) * 24 + substr(current_timestamp - a, 12, 2)
|| substr(current_timestamp - a, 14, 6)

 

Whare "a" is the column reference.

 

 

Acknowledgement

https://community.oracle.com/tech/developers/discussion/4488715/compute-the-difference-between-sysdate-and-an-interactive-report-date/p1