Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Total Seconds to display hours/minutes/seconds

539918Dec 22 2006 — edited Dec 22 2006
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 19 2007
Added on Dec 22 2006
3 comments
800 views