How to convert a Calculated Time Difference to Decimals in Toad for Oracle?

Question

I am Querying a Table called SENSOR_INSPECTIONS in a Schema called SENSORMAINTAB. The table is fed from a machine that processes Lots/Batches that are composed of 10, 000 Sensors. The Machine makes multiple 'Inspections' of each Sensor as the Sensors pass through the Machine.

I have written a Query below to determine the difference (in a custom column called 'Duration') between the Latest and Earliest Inspection Time of a Lot/Batch (which gives me the duration that the machine was running for).

The Query is:

SELECT LOT_ID, 
    MAX(INSPECTION_TIME), 
    MIN(INSPECTION_TIME), 
    MAX(INSPECTION_TIME) - MIN(INSPECTION_TIME) AS Duration
FROM SENSORMAINTAB.SENSOR_INSPECTIONS
WHERE LOT_ID = 1392353501 AND INSPECTION_ID = 49
GROUP BY LOT_ID

The Result I get is the following:

LOT_ID	MAX(INSPECTION_TIME)	MIN(INSPECTION_TIME)	DURATION
1392353501	27/05/2023 20:06:03.529000	27/05/2023 17:36:50.657000	+00 02:29:12.872000

Is it possible for me to add an extra Column to this result output that shows the time difference in Hrs expressed as a decimal?

29 Mins, 12 Seconds and 0.872 Seconds is 0.48690888889 Hrs according to Google. I rounded this to 0.487 (3 Decimal Places).

So for the above Example, it would be:

LOT_ID	MAX(INSPECTION_TIME)	MIN(INSPECTION_TIME)	DURATION	ROUNDED
1392353501	27/05/2023 20:06:03.529000	27/05/2023 17:36:50.657000	+00 02:29:12.872000	2.487

Answer 1

The substraction of the two timestamps gives you an interval day to second. If you want to retain the sub-second precision, you need to extract each part of the interval and do the math.

select t.*,
    round(
          extract(day    from duration)  * 24
        + extract(hour   from duration) 
        + extract(minute from duration) / 60
        + extract(second from duration) / 60 / 60,
        3
    ) as rounded
from (
    select lot_id, 
        max(inspection_time) as max_time, 
        min(inspection_time) as min_time,  
        max(inspection_time) - min(inspection_time) as duration
    from sensormaintab.sensor_inspections 
    where lot_id = 1392353501 and inspection_id = 49 
    group by lot_id
) t

Using a subquery avoids repeating the max(...) - min(...) expression (we could also do this in a lateral join).

Answer 2

If you are not bothered by the fractional seconds then cast the timestamps to dates and subtract to get a fraction of a day and then multiply by 24:

SELECT LOT_ID, 
    MAX(INSPECTION_TIME), 
    MIN(INSPECTION_TIME), 
    (CAST(MAX(INSPECTION_TIME) AS DATE) - CAST(MIN(INSPECTION_TIME) AS DATE)) * 24
      AS Duration
FROM SENSORMAINTAB.SENSOR_INSPECTIONS
WHERE LOT_ID = 1392353501 AND INSPECTION_ID = 49
GROUP BY LOT_ID

If you want fractional seconds then truncate the timestamps to the start of the minute and find the difference and then include the seconds difference:

SELECT LOT_ID, 
    MAX(INSPECTION_TIME), 
    MIN(INSPECTION_TIME), 
    (
      TRUNC(MAX(INSPECTION_TIME), 'MI')
      - TRUNC(MIN(INSPECTION_TIME), 'MI')
    ) * 24
    + (
      EXTRACT(SECOND FROM MAX(INSPECTION_TIME))
      - EXTRACT(SECOND FROM MIN(INSPECTION_TIME))
    ) / 60 / 60
      AS Duration
FROM SENSORMAINTAB.SENSOR_INSPECTIONS
WHERE LOT_ID = 1392353501 AND INSPECTION_ID = 49
GROUP BY LOT_ID