I'd have so much free time
A reader, March 09, 2003 - 11:10 am UTC
If I could carry you around in my pocket.
A downtime segment could space multiple days
A reader, March 09, 2003 - 11:46 am UTC
I think it's appropriate to end a day's downtime segment at 23:59:59 since the detail of the report is by day. However, I would need to include those days for which there might not be an entry in the table (the system was down all day). How do I include those days in my detail listing? Or what if I wanted to list each downtime intervals from start to end. A downtime interval could span multiple days so I would want to report the difference between an uptime START and the previous uptime END.
When I determine the MAX downtime I need to include these downtime intervals that span multiple days.
2/13/2003 00:07:59 2/13/2003 13:59:59
2/13/2003 15:59:23 2/13/2003 22:00:00
2/15/2003 11:00:00 2/15/2003 23:59:59
The report total needs to show a maximum downtime segment for the downtime between 2/13/2003 22:00:00 and 2/15/2003 11:00:00. So, the days reported have to be all days within the selected period including those without data in the table.
Thanks very much for your help
March 09, 2003 - 12:04 pm UTC
now you are talking nasty.
RDBMS's *hate* to make up data that doesn't exist. And sticking in dummy records is going to be equally as painful.
You would want to have the break records AS WELL as the non-break records.
Sorry -- no clean pretty SQL answer this time. We can show something like:
startdate of downtime
maximum ENDDATE of downtime started that day (this - start date gives number of days down)
amount of downtime incurred total over all possible days
count of times the system was down that day
min period
max period
but getting each day output for a multi-day span where the days are more then 2 days apart gets gnarly.
I won't try that approach
A reader, March 10, 2003 - 7:45 am UTC
Thanks then, I won't attempt to fill the detail with dates that don't exist in the table. I'll only show the segments that occur within a 24 hour period from a day posted to the table.
For my report summary to show downtime that spans multiple days, I can use LEAD, correct? I'll get the downtime that might start on one day (ENDDATE) and end several days later (the next STARTDATE). I'll use those periods to determine MAXDOWN in my report summary. And that would have to be a second run through the table I suppose.
Thanks very much.
March 10, 2003 - 7:49 am UTC
I'm confused -- i thought startdate was the beginning of the downtime and enddate was the end of the downtime?? are you saying enddate is the start of the downtime and startdate is the end of the downtime?
Records show UPTIME
A reader, March 10, 2003 - 8:03 am UTC
I'm very sorry, the records show UPTIME. If the system is up all day, there will be one record and no downtime.
STARTDATE ENDDATE
3/1/2003 00:00:00 3/1/2003 23:59:59
3/2/2003 00:00:00 3/2/2003 08:00:00
3/2/2003 12:00:00 3/2/2003 23:59:59
I need to calculate both UPTIME and DOWNTIME and PERCENTAGE DOWN for each day. For the selected period, I need to show TOTAL DOWN, MAX DOWN, MIN DOWN, and PERCENTAGE DOWN.
Sorry for the lack of detail and thanks for your patience.
March 10, 2003 - 8:18 am UTC
Ok, then you would need to apply my query to this view:
select *
from (
select enddate startdate,
lead(startdate) over ( order by startdate ) enddate
from t
)
where startdate <> enddate-1/24/60/60;
i think (to get the representation of the records as downtime intervals, instead of uptime intervals)
A reader, May 10, 2004 - 4:57 pm UTC
Is there also a way to do this more generic?
Joachim Mayer, October 29, 2004 - 6:28 am UTC
Hello Tom,
it was very interresting to read this - did not even know about rollup before but i would need this in a more generic way, where - i think - your this solution does not apply.
I have this table t, containing start and end time and i need report on a time interval (f.e. 1 hour ) and i want to know the uptime in this interval.
see here my table
drop table t;
create table t ( id number(9), starttime date, endtime date);
insert into t ( id, starttime, endtime ) values ( 1 , sysdate - 1, sysdate - 0.9 );
insert into t ( id, starttime, endtime ) values ( 2 , sysdate - 0.85, sysdate - 0.8 );
insert into t ( id, starttime, endtime ) values ( 3 , sysdate - 0.66, sysdate - 0.52 );
insert into t ( id, starttime, endtime ) values ( 4 , sysdate - 0.31, sysdate - 0.18 );
insert into t ( id, starttime, endtime ) values ( 5 , sysdate - 0.18, sysdate - 0.15 );
insert into t ( id, starttime, endtime ) values ( 6 , sysdate - 0.10, sysdate - 0.02 );
select * from t;
ID STARTTIME ENDTIME
---------- ------------------- -------------------
1 2004-10-28 11:40:47 2004-10-28 14:04:47
2 2004-10-28 15:16:56 2004-10-28 16:28:56
3 2004-10-28 19:50:41 2004-10-28 23:12:17
4 2004-10-29 04:14:54 2004-10-29 07:22:06
5 2004-10-29 07:22:13 2004-10-29 08:05:25
6 2004-10-29 09:17:33 2004-10-29 11:12:45
but i would need it like this where the interval is not a day but free (f.e. 1 hour interval ) and i want to know the total uptime within this time-periode.
2004-10-28 11:00:00 .01334491 (19min, 13 sec )
2004-10-28 12:00:00 .04166667 ( 1h )
2004-10-28 13:00:00 .04166667 ( 1h )
2004-10-28 14:00:00 .00332176 ( 4min 47 sec )
etc...
can this be done with analytics (SQL). I cant find a good solution...
thx
Joachim
October 29, 2004 - 8:45 am UTC
ops$tkyte@ORA9IR2> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
Session altered.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> with X as
2 (select to_date( '2004-10-28 08:00:00' )+(rownum-1)*1/24 dt1,
3 to_date( '2004-10-28 08:00:00' )+rownum*1/24-1/24/60/60 dt2
4 from all_objects
5 where rownum <= 5 ) ,
6 Y as
7 (select dt1 hour_start, dt2 hour_end,
8 least(dt2,endtime)-greatest(dt1,starttime) ela
9 from t, x
10 where x.dt1 < t.endtime
11 and x.dt2 > t.starttime)
12 select dt1, ela
13 from x left outer join y on (dt1 = hour_start)
14 /
DT1 ELA
------------------- ----------
2004-10-28 08:00:00 .041655093
2004-10-28 09:00:00 .031342593
2004-10-28 10:00:00 .001979167
2004-10-28 11:00:00 .041655093
2004-10-28 12:00:00 .006342593
X is the set of "times" you are interested in -- use anything you want to generate it.