I have this query that is getting a total number of seconds, and I want to change it into Total Hours, Minutes, seconds.
Below I have what I am returning, it is very close to what I want, but I think I am doing a couple of bad calculations. In the last line that I am returning from my query, it is showing 4 hours, 59 minutes, and 40 seconds. But I know that it is suppose to be 4 hours, 58 minutes, and 40 seconds, but when I get the 58 to be correct, it screws up the other minutes.
Thanks for any help.
select
sum(diff) as "Total Seconds",
floor(sum(diff)/60/60) "hours",
round(round((sum(diff)/60/60) - floor(sum(diff)/60/60), 2) * 60) "minutes",
round(round(sum(diff)/60 - floor(sum(diff)/60), 2) * 60) "seconds"
from
(
select
"trek_id",
(lead("date_time") over (partition by "trek_id" order by "date_time") - "date_time")*86400 diff
from qamaster."cust_positions"
where "speed" >= 105
and "operator_id" in (2)
)
group by "trek_id"
Returning
Total Seconds hours minutes seconds
------------- ---------- ---------- ----------
120 0 2 0
180 0 3 0
180 0 3 0
17920 4 59 40