SQL Query to find the time taken in hours, minutes and seconds.

Here is the oracle sql query that can be used to find out the time taken in hours, minutes and second.

This sql query converts the date time format and calculates accordingly.

This can be modified accordingly for the requirements and can be tested.

select bb.start_time,bb.end_time,
mod(trunc( 24 * (to_date(bb.end_time,'DD-MON-YYYY HH24:MI:SS') - to_date(bb.start_time,'DD-MON-YYYY HH24:MI:SS'))),60) AS BATCH_TOTAL_TIME_HOURS,
mod(trunc( 24 * 60 * (to_date(bb.end_time,'DD-MON-YYYY HH24:MI:SS') - to_date(bb.start_time,'DD-MON-YYYY HH24:MI:SS'))),60) AS BATCH_TOTAL_TIME_MINS,
mod(trunc( 24 * 60 * 60 *(to_date(bb.end_time,'DD-MON-YYYY HH24:MI:SS') - to_date(bb.start_time,'DD-MON-YYYY HH24:MI:SS'))),60) AS BATCH_TOTAL_TIME_SECONDS
from
XX_CUSTOM_BATCH bb;