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