sqldate-arithmetic

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


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.

The Query is:

SELECT LOT_ID, 
    MAX(INSPECTION_TIME), 
    MIN(INSPECTION_TIME), 
    MAX(INSPECTION_TIME) - MIN(INSPECTION_TIME) AS Duration
FROM table_name
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

Solution

  • 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 table_Name 
        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).