Home » SQL & PL/SQL » SQL & PL/SQL » Calculating time difference
Calculating time difference [message #408823] Thu, 18 June 2009 00:05 Go to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Dear all,


The requirement is like this i have source_id,start_time,end_time now i want to caluclate the time diffrence in minutes so that it should exclude the repeated time intervals.

Like in this example..

1st record time difference is 60 min.
2nd record has to be 60 min but 1:30 to 2:00 is already there in the first column so.. we are caluclatinf from 2:00 to 2:30 it will give 30 min.

1 01:00:00 02:00:00 60 min
1 01:30:00 02:30:00 30 min
1 02:15:00 02:17:00 0 min
1 23:25:00 23:59:09 9 min

Reply is appreciated...

Thanks,
Srivardhan.

[EDITED by LF: fixed topic title typos; the original (after topics being merged) was "Caluclating Time Diffrence"]

[Updated on: Thu, 18 June 2009 05:27] by Moderator

Report message to a moderator

Re: Caluclating Time Diffrence (merged 3) [message #408835 is a reply to message #408823] Thu, 18 June 2009 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.
It is not clear why 4th line is 9, I'd think it was 34 (if you omit the seconds).

Regards
Michel

[Updated on: Thu, 18 June 2009 00:35]

Report message to a moderator

Re: Caluclating Time Diffrence (merged 3) [message #408840 is a reply to message #408835] Thu, 18 June 2009 00:50 Go to previous messageGo to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Hi Michel,

Thanks for your reply, 4th line is 34 only.next time i will see that these mistakes will not happen..


Thanks & Regrads,
Srivardhan

Re: Calculating Time Difference (merged 3) [message #408843 is a reply to message #408840] Thu, 18 June 2009 00:59 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
How about a test case? DDL and DML.

By
Vamsi
Re: Caluclating Time Diffrence (merged 3) [message #408844 is a reply to message #408835] Thu, 18 June 2009 00:59 Go to previous messageGo to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Hi Michel,

The requirement is like this i have id,start_time,end_time now i want to caluclate the time diffrence in minutes so that it should exclude the repeated time intervals.


Create table test_v4 as (id int, start_time date, end_time date);

insert into test_v4 values ('1','31/07/2008 01:00:00','31/07/2008 02:00:00')
insert into test_v4 values ('1','31/07/2008 01:30:00','31/07/2008 02:30:00')
insert into test_v4 values ('1','31/07/2008 02:15:00','31/07/2008 02:17:00')
insert into test_v4 values ('1','31/07/2008 23:25:00','31/07/2008 23:59:00')


Result:


1 31/07/2008 01:00:00 31/07/2008 02:00:00 60 min
1 31/07/2008 01:30:00 31/07/2008 02:30:00 30 min
1 31/07/2008 02:15:00 31/07/2008 02:17:00 0 min
1 31/07/2008 23:25:00 31/07/2008 23:59:09 34 min


Thanks & Regards,
Srivardhan

[Updated on: Thu, 18 June 2009 01:00]

Report message to a moderator

Re: Calculating Time Difference (merged 3) [message #408847 is a reply to message #408843] Thu, 18 June 2009 01:03 Go to previous messageGo to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Hi Vamsi,

Test case can be anything

Thanks
Srivardhan
Re: Calculating Time Difference (merged 3) [message #408867 is a reply to message #408847] Thu, 18 June 2009 02:14 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
This would be a better test case:
CREATE TABLE test_v4 ( 
  id         INTEGER,  -- remove "as", changed to integer
  start_time DATE, 
  end_time   DATE); 

INSERT INTO test_v4 -- added to_date
VALUES     (1, -- removed code
            To_date('31/07/2008 01:00:00','dd/mm/yyyy hh24:mi:ss'), 
            To_date('31/07/2008 02:00:00','dd/mm/yyyy hh24:mi:ss')); 

INSERT INTO test_v4 
VALUES     (1, 
            To_date('31/07/2008 01:30:00','dd/mm/yyyy hh24:mi:ss'), 
            To_date('31/07/2008 02:30:00','dd/mm/yyyy hh24:mi:ss')); 

INSERT INTO test_v4 
VALUES     (1, 
            To_date('31/07/2008 02:15:00','dd/mm/yyyy hh24:mi:ss'), 
            To_date('31/07/2008 02:17:00','dd/mm/yyyy hh24:mi:ss')); 

INSERT INTO test_v4 
VALUES     (1, 
            To_date('31/07/2008 23:25:00','dd/mm/yyyy hh24:mi:ss'), 
            To_date('31/07/2008 23:59:00','dd/mm/yyyy hh24:mi:ss'));
By
Vamsi
Re: Calculating Time Difference (merged 3) [message #408869 is a reply to message #408867] Thu, 18 June 2009 02:21 Go to previous messageGo to next message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Hi Vamsi,

Thanks for your reply. what u had return is correct.
can we get the out put as

1 31/07/2008 01:00:00 31/07/2008 02:00:00 60 min
1 31/07/2008 01:30:00 31/07/2008 02:30:00 30 min
1 31/07/2008 02:15:00 31/07/2008 02:17:00 0 min
1 31/07/2008 23:25:00 31/07/2008 23:59:09 34 min


from the table test_v4 where we have already inserted values.
where 60 min, 30 min, 0 min, 34 min is the time difference.

Awaiting for the reply.


Thanks
Srivardhan
Re: Calculating Time Difference (merged 3) [message #408878 is a reply to message #408847] Thu, 18 June 2009 02:41 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
NOTE: No warranties! It's put together quick and dirty. There might be better solutions around and even more important: my logic may be incorrect. I'm not entirely awake yet... http://www.orafaq.com/forum/fa/1598/0/

The logic is this: Each interval starts where the previous ended. But only if the start is situated before the previous ending in time. Each interval ends at the largest interval end date encountered. That's my basic logic (which - again - may be incorrect).

There is a flaw in your requirement: I've assumed that the order was by start date but you've never indicated this as fact.

The script:
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi'
/

with yourtable as 
(
 select 1 an_id
      , to_date('31/07/2008 01:00', 'dd/mm/yyyy hh24:mi') start_date
      , to_date('31/07/2008 02:00', 'dd/mm/yyyy hh24:mi') end_date 
 from dual 
 union all
 select 1 an_id
      , to_date('31/07/2008 01:30', 'dd/mm/yyyy hh24:mi') start_date
      , to_date('31/07/2008 02:30', 'dd/mm/yyyy hh24:mi') end_date
 from dual 
 union all
 select 1 an_id
      , to_date('31/07/2008 02:15', 'dd/mm/yyyy hh24:mi') start_date
      , to_date('31/07/2008 02:17', 'dd/mm/yyyy hh24:mi') end_date
 from dual 
 union all
 select 1 an_id
      , to_date('31/07/2008 23:25', 'dd/mm/yyyy hh24:mi') start_date
      , to_date('31/07/2008 23:59', 'dd/mm/yyyy hh24:mi') end_date 
 from dual
)
select an_id
     , start_date
     , end_date
     -- complex(er) solution using EXTRACT:
     -- both HOUR and MINUTE are used.
     , extract ( hour from (greatest(maxdate,start_date) 
       - 
       -- take previous end date (if not found, take start date) and start date.
       -- then take the largest of the two.
       greatest(nvl(lag(end_date) over ( order by start_date), start_date), start_date)) day to second 
      )*60
       + 
       extract ( minute from (greatest(maxdate,start_date) 
       - 
       greatest(nvl(lag(end_date) over ( order by start_date), start_date), start_date)) day to second 
      ) diff1
      -- easier solution using basic date arithmetic:
     , ( maxdate
         - greatest(nvl(lag(end_date) over ( order by start_date), start_date), start_date) -- diff in units of days
       ) *(24*60) diff2 -- number of minutes in days = 24*60
from ( -- sorted inner select
       select an_id
            , start_date
            , end_date
            -- what is the greatest end date (starting point of our interval):
            , max(end_date) over ( order by start_date) maxdate
       from yourtable
       order by start_date
     )
/
The SQL*Plus session:
SQL> @orafaq

Session altered.


     AN_ID START_DATE       END_DATE              DIFF1      DIFF2
---------- ---------------- ---------------- ---------- ----------
         1 31/07/2008 01:00 31/07/2008 02:00         60         60
         1 31/07/2008 01:30 31/07/2008 02:30         30         30
         1 31/07/2008 02:15 31/07/2008 02:17          0          0
         1 31/07/2008 23:25 31/07/2008 23:59         34         34
I've given you two solutions in one query.

MHE
Re: Caluclating Time Diffrence (merged 3) [message #408894 is a reply to message #408823] Thu, 18 June 2009 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Thanks Vamsi for the working test case.
SQL> with 
  2    data as (
  3      select id, start_time, end_time,
  4             nvl(max(end_time) 
  5                   over (partition by id order by start_time
  6                         rows between unbounded preceding and 1 preceding),
  7                 start_time)
  8              latest_end_time
  9      from test_v4
 10    )
 11  select id, start_time, end_time, 
 12         ( greatest(end_time, latest_end_time) - greatest(start_time, latest_end_time) )
 13         * 1440 min
 14  from data
 15  order by 1, 2
 16  /
        ID START_TIME          END_TIME                   MIN
---------- ------------------- ------------------- ----------
         1 31/07/2008 01:00:00 31/07/2008 02:00:00         60
         1 31/07/2008 01:30:00 31/07/2008 02:30:00         30
         1 31/07/2008 02:15:00 31/07/2008 02:17:00          0
         1 31/07/2008 23:25:00 31/07/2008 23:59:00         34

4 rows selected.

Regards
Michel

[Updated on: Thu, 18 June 2009 03:58]

Report message to a moderator

Re: Caluclating Time Diffrence (merged 3) [message #408895 is a reply to message #408894] Thu, 18 June 2009 04:10 Go to previous message
srivardhan
Messages: 20
Registered: June 2009
Junior Member
Dear All,

Thank you so much for giving me the solution. You guys has done wonderful job.

Thanks&Regards
Srivardhan
Previous Topic: multiple insertion problem
Next Topic: Trimming Help
Goto Forum:
  


Current Time: Thu Feb 13 16:51:39 CST 2025