CODEWITHSUNDEEP
sqloracle-databaseoracle11g
genericHCU
genericHCU

Reputation: 4446

formatting total time spent using extract

I'm open to any suggestions, so long as I get something resembling the output at the bottom of the post, TIA!

Can I get some help with this sql fiddle: http://sqlfiddle.com/#!4/c51c5/1

SCHEMA

create table history(
      clockingGroup varchar2(5)
    , startTime timestamp
    , endTime timestamp);

insert into history
  (clockingGroup, startTime, endTime)
 values
  ('grp1', '01-dec-2015 1:00:00.000000', '01-dec-2015 1:10:10.000000');
insert into history
  (clockingGroup, startTime, endTime)
 values
  ('grp2', '01-dec-2015 1:10:10.000000', '01-dec-2015 1:20:20.000000');
insert into history
  (clockingGroup, startTime, endTime)
values
  ('grp1', '01-dec-2015 1:20:20.000000', '01-dec-2015 1:30:35.000000');
insert into history
  (clockingGroup, startTime, endTime)
values
  ('grp3', '01-dec-2015 1:30:35.000000', '01-dec-2015 1:35:00.000000');

SQL 

select 
     extract(hour from (sum(cast(endTime as date) - cast(startTime as date)))) || ' Hours ' 
  || extract(minute from (sum(cast(endTime as date) - cast(startTime as date)))) || ' Minutes ' 
  || extract(second from (sum(cast(endTime as date) - cast(startTime as date)))) || ' Seconds' 

  as totalTime
  , clockingGroup
from 
  history
group by 
  clockingGroup

Current Error ORA-30076: invalid extract field for extract source

My desired output is: 

clockingGroup | totalTime    
grp1          | 0 Hours 20 Minutes 25 Seconds
grp2          | 0 Hours 10 Minutes 10 Seconds
grp3          | 0 Hours 4 Minutes 25 Seconds

Upvotes: 1

Views: 68

Answers (1)

kevinskio
kevinskio

Reputation: 4551

You are using the built ins in the wrong order. Your original query casts your timestamps to dates. When you subtract dates from each other you get a number. In the query below it subtracts a timestamp from a timestamp which yields an INTERVAL. You cannot extract the hour from a number but you can from an INTERVAL. 

SELECT clockingGroup.
SUM(extract(DAY FROM endtime-starttime))    ||' '|| 
SUM(extract(HOUR FROM endtime-starttime))   ||' '|| 
SUM(extract(MINUTE FROM endtime-starttime)) ||' '|| 
SUM(extract(SECOND FROM endtime-starttime)) AS TOTALTIME,

from 
  history
group by 
  clockingGroup

I see you have added another method using numtodsinterval. You are still converting timestamps to dates which you don't need to do if you want precision.

This query is more complicated as I try and show how you take the difference and count all the intervals as seconds, sum them and then break it apart into hours/minutes/seconds

SELECT CLOCKINGGROUP, TO_CHAR(TRUNC(SUMTOTALSECONDS/3600),'FM9900') || ' Hours ' ||
    TO_CHAR(TRUNC(MOD(SUMTOTALSECONDS,3600)/60),'FM00') || ' Minutes ' ||
    TO_CHAR(MOD(SUMTOTALSECONDS,60),'FM00') || ' Seconds'
FROM(
SELECT clockinggroup, TRUNC(SUM(TOTALSECONDS),0) AS SUMTOTALSECONDS
FROM (
SELECT clockinggroup,
EXTRACT (DAY FROM (EndTime-StartTime))*24*60*60 +
EXTRACT (HOUR FROM (EndTime-StartTime))*60*60 +
EXTRACT (MINUTE FROM (EndTime-StartTime))*60 + 
EXTRACT (SECOND FROM (EndTime-StartTime))/60 AS TOTALSECONDS  
FROM history)
group by 
clockingGroup)
ORDER BY 1;

Upvotes: 1

Related Questions