Get number of seconds in specific duration

Question

I have a table contains information about calls, each call have a start date and end date with DATE type with YYYY:MM:DD HH:MI:SS format.

how to get the following:

1- number of seconds in the range from 00:00:00 to 07:30:00 between the start date and the end date, and the number of seconds out of the given range (00:00:00 to 07:30:00).

2- number of seconds in Fridays days between the start date and the end date .

this should give you the answer http://stackoverflow.com/questions/21097004/find-the-elapsed-time-between-two-dates-in-oracle-sql — davegreen100, Dec 29 '15 at 13:59

Ponder Stibbons · Accepted Answer · 2015-12-30T12:34:54.857

My try with hierarchical subquery generating days for each call and functions greatest, least:

^SQLFiddle

with t as (
  select id, sd, ed, trunc(sd)+level-1 dt
    from calls 
    connect by trunc(sd)+level-1<=trunc(ed) 
      and prior dbms_random.value is not null and prior id = id)
select id, sum(sec) sec, sum(fri) fri, sum(mrn) mrn, sum(sec)-sum(mrn) rest
  from (
    select id, (least(ed, dt+1)-greatest(sd, dt))*24*60*60 sec,
           case when trunc(dt) - trunc(dt, 'iw') = 4 
                then (least(dt+1, ed) - greatest(dt, sd)) * 24*60*60 end fri,
           (least(dt+7.5/24, ed) - greatest(dt, sd)) * 24*60*60 mrn
      from t )
  group by id

Query version for "Fridays" - "non Fridays mornings" - "non Fridays rest of days" output (as precised in comments):

with cte as (
  select id, sd, ed, trunc(sd)+level-1 dt from calls
    connect by level <= trunc(ed)-trunc(sd) + 1 
      and prior dbms_random.value is not null and prior id = id )
select id, max(sd) start_time, max(ed) end_time, 
       sum(sec) all_seconds, sum(fri) fridays, sum(mrn) mornings,
       sum(sec) - sum(fri) - sum(mrn) rest
  from (
    select id, sd, ed, dt, (least(ed, dt+1) - greatest(sd, dt))*24*60*60 sec,
        case when dt - trunc(dt, 'iw') = 4 
             then (least(ed, dt+1) - greatest(sd, dt))*24*60*60 else 0 end fri,
        case when dt - trunc(dt, 'iw') <> 4 and dt+7.5/24 > sd 
             then (least(dt+7.5/24, ed) - greatest(sd, dt))*24*60*60 
             else 0 end mrn
      from cte )
  group by id order by id

Sample data and output:

create table calls (id number(3), sd date, ed date);
insert into calls values (1, timestamp '2015-12-25 07:29:00', timestamp '2015-12-25 07:31:00');
insert into calls values (2, timestamp '2015-12-24 01:00:00', timestamp '2015-12-26 23:12:42');
insert into calls values (3, timestamp '2015-12-24 23:58:00', timestamp '2015-12-25 00:01:00');
insert into calls values (4, timestamp '2015-12-24 07:00:00', timestamp '2015-12-25 00:01:00');

  ID START_TIME          END_TIME            ALL_SECONDS    FRIDAYS   MORNINGS       REST
---- ------------------- ------------------- ----------- ---------- ---------- ----------
   1 2015-12-25 07:29:00 2015-12-25 07:31:00         120        120          0          0
   2 2015-12-24 01:00:00 2015-12-26 23:12:42      252762      86400      50400     115962
   3 2015-12-24 23:58:00 2015-12-25 00:01:00         180         60          0        120
   4 2015-12-24 07:00:00 2015-12-25 00:01:00       61260         60       1800      59400

Edit:

what is greatest and least function ? can you explain to me more please ? — Mohamad Ghanem, Dec 29 '15 at 16:47
can you update you answer please to not add the seconds to MRN & REST if the day is friday, For example : start date = 2015-12-25 07:29:00 - end date=2015-12-25 07:31:00, I want the answer as : (SEC=120 - FRI=120 - MRN=0 - REST=0) I mean that I want the number of seconds in the range from 00:00:00 to 07:30:00 between the start date and the end date, and the number of seconds out of the given range (00:00:00 to 07:30:00) if the day is not Friday — Mohamad Ghanem, Dec 29 '15 at 17:22
I can. But you have everything on the plate. The only thing is **logic**. You can put it in `case when` statements. — Ponder Stibbons, Dec 30 '15 at 01:04
Can you update the answer please, because I try to do it but it gives compiler error every time, and the query give wrong answer when the start data and end date don't contain duration from 00:00:00 to 07:30:00 :( — Mohamad Ghanem, Dec 30 '15 at 08:13

score 0 · Answer 2 · answered Dec 29 '15 at 15:17

With :

SELECT (end_date - start_date), ... FROM ...

you obtain the number of days...

With :

SELECT (end_date - start_date)*24, ... FROM ...

you obtain the number of hours...

And, with :

SELECT (end_date - start_date)*24*60*60, ... FROM ...

you obtain the number of seconds...

54l3d · Answer 3 · 2015-12-30T09:09:40.477

This works fine, with clause is just for test :

with a as(
select
TO_TIMESTAMP('122320158:00:00','MMDDYYYYHH:MI:SS') start_date,TO_TIMESTAMP('122320158:01:06','MMDDYYYYHH:MI:SS') end_date from dual
union all
select
TO_TIMESTAMP('112420152:00:00','MMDDYYYYHH:MI:SS') start_date,TO_TIMESTAMP('112420152:00:53','MMDDYYYYHH:MI:SS') end_date from dual
union all
select
TO_TIMESTAMP('102720157:31:00','MMDDYYYYHH:MI:SS') start_date,TO_TIMESTAMP('102720157:31:10','MMDDYYYYHH:MI:SS') end_date from dual
) -- until here, with clause just give sample data
select  'before7.30' range,sum(EXTRACT(minute FROM(end_date-start_date))*60+EXTRACT(hour FROM(end_date-start_date))*3600+EXTRACT(second FROM(end_date-start_date))) as seconds
from (
select * from a
)
where start_date < trunc(start_date)+(1/24)*7.5 -- start_date<7.30
union all
select  'after7.30' range,sum(EXTRACT(minute FROM(end_date-start_date))*60+EXTRACT(hour FROM(end_date-start_date))*3600+EXTRACT(second FROM(end_date-start_date))) as seconds
from (
select * from a
)
where start_date >= trunc(start_date)+(1/24)*7.5 -- start_date>=7.30

Output:

before7.30  53
after7.30   76

If your table named cdr_table just remove the with clause:

select  'before7.30' range,sum(EXTRACT(minute FROM(end_date-start_date))*60+EXTRACT(hour FROM(end_date-start_date))*3600+EXTRACT(second FROM(end_date-start_date))) as seconds
from (
select * from cdr_table
)
where start_date < trunc(start_date)+(1/24)*7.5
union all
select  'after7.30' range,sum(EXTRACT(minute FROM(end_date-start_date))*60+EXTRACT(hour FROM(end_date-start_date))*3600+EXTRACT(second FROM(end_date-start_date))) as seconds
from (
select * from cdr_table
)
where start_date >= trunc(start_date)+(1/24)*7.5

Get number of seconds in specific duration

3 Answers3