Home » SQL & PL/SQL » SQL & PL/SQL » Calculating time difference
Calculating time difference [message #408823] |
Thu, 18 June 2009 00:05  |
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   |
 |
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 #408844 is a reply to message #408835] |
Thu, 18 June 2009 00:59   |
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 #408867 is a reply to message #408847] |
Thu, 18 June 2009 02:14   |
 |
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   |
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   |
 |
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... 
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   |
 |
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
|
|
|
|
Goto Forum:
Current Time: Thu Feb 13 16:51:39 CST 2025
|