472,333 Members | 1,053 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,333 software developers and data experts.

Find overlapping time intervals, how?

Say, we have uptimes from several servers:

Server up_from up_to
------ ------- -------
s1 0:00 8:00
s1 10:00 20:00
s1 22:00 24:00 (would better be a timestamp with 0:00 and next day)
s2 0:00 4:00
s2 6:00 23:00
Now we want to generate a report of the availability. But for a specific
application both servers must be up at the same time. So the combined
uptime would be from 0:00 to 4:00, from 6:00 to 8:00, from 10:00 to
20:00 and from 22:00 to 23:00. Then we can calculate an uptime
percentage.

(Another setup would be if the application is clustered, then the
uptimes would be ORed instead of ANDed)

What would be the most elegant way to do this? I started with a self
join and 4 unions covering the 4 cases for start- end endtime:

- Interval of s1 starts earlier and ends earlier than interval of s2
(up_from of s1 <= up_from of s2 and
up_to of s1 <= up_to of s2 and
up_to of s1 > up_from of s2) -- overlap condition
Then the uptime interval is [up_from of s2, up_to of s1]
#####
#####

- Interval of s2 starts earlier and ends earlier than interval of s1
Vice versa.
#####
#####

- Interval of s1 contains interval of s2
(up_from of s1 <= up_from of s2 and
up_to of s1 >= up_to of s2)
Then the uptime interval is [up_from of s1, up_to of s1]
########
####

- Interval of s2 contains interval of s1
Vice versa.
####
########

But this looks ugly.

Any ideas? I thought of area functions when using rectangles instead of
times, but I don't know if that's good.

--
PGP/GPG Key-ID:
http://blackhole.pca.dfn.de:11371/pk...rch=0xB5A1AFE1

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #1
8 10043
On Tuesday 09 September 2003 02:29 pm, Holger Marzen wrote:
Say, we have uptimes from several servers:

Server up_from up_to
------ ------- -------
s1 0:00 8:00
s1 10:00 20:00
s1 22:00 24:00 (would better be a timestamp with 0:00 and next
day) s2 0:00 4:00
s2 6:00 23:00
Now we want to generate a report of the availability. But for a specific
application both servers must be up at the same time. So the combined
uptime would be from 0:00 to 4:00, from 6:00 to 8:00, from 10:00 to
20:00 and from 22:00 to 23:00. Then we can calculate an uptime
percentage.

(Another setup would be if the application is clustered, then the
uptimes would be ORed instead of ANDed)

What would be the most elegant way to do this? I started with a self
join and 4 unions covering the 4 cases for start- end endtime:

- Interval of s1 starts earlier and ends earlier than interval of s2
(up_from of s1 <= up_from of s2 and
up_to of s1 <= up_to of s2 and
up_to of s1 > up_from of s2) -- overlap condition
Then the uptime interval is [up_from of s2, up_to of s1]
#####
#####

- Interval of s2 starts earlier and ends earlier than interval of s1
Vice versa.
#####
#####

- Interval of s1 contains interval of s2
(up_from of s1 <= up_from of s2 and
up_to of s1 >= up_to of s2)
Then the uptime interval is [up_from of s1, up_to of s1]
########
####

- Interval of s2 contains interval of s1
Vice versa.
####
########

But this looks ugly.


I haven't tested this; but what if you:

1. Use timestamps instead of times to account for crossing over midnight.

2. Using aliases t1 and t2 for the source data table:

SELECT case when t1.up_from >= t2.up_from then t1.up_from as start_time else
t2.up_from as start_time,

case when t1.up_to <= t2.up_to then t1.up_to as end_time else t2.up_to as
end_time,

end_time - start_time as avail_time

INTO temp table temp_avail

FROM tablename as t1, tablename as t2

WHERE t1.server <> t2.server and
(t2.upfrom between t1.up_from and t1_up_to OR
t2.up_to between t1.up_from and t1_up_to) and
t1.server = 's1';

3. Now you can run a query on temp_avail for stats.

???

Andrew Gould

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #2
ho****@marzen.de (Holger Marzen) writes:
Say, we have uptimes from several servers:

Server up_from up_to
------ ------- -------
s1 0:00 8:00
s1 10:00 20:00
s1 22:00 24:00 (would better be a timestamp with 0:00 and next day)
s2 0:00 4:00
s2 6:00 23:00
Now we want to generate a report of the availability. But for a specific
application both servers must be up at the same time. So the combined
uptime would be from 0:00 to 4:00, from 6:00 to 8:00, from 10:00 to
20:00 and from 22:00 to 23:00. Then we can calculate an uptime
percentage.

(Another setup would be if the application is clustered, then the
uptimes would be ORed instead of ANDed)

What would be the most elegant way to do this? I started with a self
join and 4 unions covering the 4 cases for start- end endtime:


Have you considered the OVERLAPS predicate?

cctld=# create table server_on (
cctld(# name character varying,
cctld(# up_from timestamptz,
cctld(# up_to timestamptz);
CREATE TABLE
cctld=# insert into server_on (name, up_from, up_to) values ('s1', '2003-08-01 00:00', '2003-08-01 08:00');
INSERT 38680519 1
cctld=# insert into server_on (name, up_from, up_to) values ('s1', '2003-08-01 10:00', '2003-08-01 20:00');
INSERT 38680520 1
cctld=# insert into server_on (name, up_from, up_to) values ('s1', '2003-08-01 22:00', '2003-08-01 24:00');
ERROR: Bad timestamp external representation '2003-08-01 24:00'
cctld=# insert into server_on (name, up_from, up_to) values ('s1', '2003-08-01 22:00', '2003-08-02 00:00');
INSERT 38680521 1
cctld=# insert into server_on (name, up_from, up_to) values ('s2', '2003-08-01 00:00', '2003-08-01 04:00');
INSERT 38680522 1
cctld=# insert into server_on (name, up_from, up_to) values ('s1', '2003-08-01 06:00', '2003-08-01 23:00');
INSERT 38680523 1
cctld=# select * from server_on a, server_on b where (a.up_from, a.up_to) overlaps (b.up_from, b.up_to);
name | up_from | up_to | name | up_from | up_to
------+------------------------+------------------------+------+------------------------+------------------------
s1 | 2003-08-01 00:00:00-04 | 2003-08-01 08:00:00-04 | s1 | 2003-08-01 00:00:00-04 | 2003-08-01 08:00:00-04
s1 | 2003-08-01 00:00:00-04 | 2003-08-01 08:00:00-04 | s2 | 2003-08-01 00:00:00-04 | 2003-08-01 04:00:00-04
s1 | 2003-08-01 00:00:00-04 | 2003-08-01 08:00:00-04 | s1 | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04
s1 | 2003-08-01 10:00:00-04 | 2003-08-01 20:00:00-04 | s1 | 2003-08-01 10:00:00-04 | 2003-08-01 20:00:00-04
s1 | 2003-08-01 10:00:00-04 | 2003-08-01 20:00:00-04 | s1 | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04
s1 | 2003-08-01 22:00:00-04 | 2003-08-02 00:00:00-04 | s1 | 2003-08-01 22:00:00-04 | 2003-08-02 00:00:00-04
s1 | 2003-08-01 22:00:00-04 | 2003-08-02 00:00:00-04 | s1 | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04
s2 | 2003-08-01 00:00:00-04 | 2003-08-01 04:00:00-04 | s1 | 2003-08-01 00:00:00-04 | 2003-08-01 08:00:00-04
s2 | 2003-08-01 00:00:00-04 | 2003-08-01 04:00:00-04 | s2 | 2003-08-01 00:00:00-04 | 2003-08-01 04:00:00-04
s1 | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04 | s1 | 2003-08-01 00:00:00-04 | 2003-08-01 08:00:00-04
s1 | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04 | s1 | 2003-08-01 10:00:00-04 | 2003-08-01 20:00:00-04
s1 | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04 | s1 | 2003-08-01 22:00:00-04 | 2003-08-02 00:00:00-04
s1 | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04 | s1 | 2003-08-01 06:00:00-04 | 2003-08-01 23:00:00-04
(13 rows)

Based on the overlaps, you can generate the period during which the
server was up during the day. I think Celko had an exercise like this
in _SQL For Smarties_.

At any rate, that's not QUITE up to being the total answer, but I
think you'll find "overlaps" will help make some complex joins go
away.
--
select 'cbbrowne' || '@' || 'libertyrms.info';
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)
Nov 11 '05 #3
Alvaro Herrera Munoz <al******@dcc.uchile.cl> writes:
I'm not sure if the OVERLAPS operator works on released versions;


Yes, it's been there since 7.2 or so, though sadly lacking in
documentation :-(. But I don't see any obvious way that it helps
for this problem.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #4
On Tue, Sep 09, 2003 at 09:29:58PM +0200, Holger Marzen wrote:
Say, we have uptimes from several servers:

Server up_from up_to
------ ------- -------
s1 0:00 8:00
s1 10:00 20:00
s1 22:00 24:00 (would better be a timestamp with 0:00 and next day)
s2 0:00 4:00
s2 6:00 23:00


You better get a timestamp field for the up_from and up_to, because it's
going to be very difficult to deal with only times (unless you want
a report of a single day).

I've done a similar thing with something like:

select
max(s1_uptime.up_from, s2_uptime.up_from) as start,
min(s1_uptime.up_to, s2_uptime.up_to) as end
from (select up_from, up_to from table where server=s1) as s1_uptime,
(select up_from, up_to from table where server=s2) as s2_uptime
where (s1_uptime.up_from, s1_uptime.up_to) overlaps
(s2_uptime.up_from, s2_uptime.up_to)

I'm not sure if the OVERLAPS operator works on released versions; I've
only used it on 7.4, and even there it was undocumented until yesterday
(I found it peeking at the source looking for something else).

You also have to build max(timestamp, timestamp) and min(timestamp,
timestamp) functions:

create function max(timestamp, timestamp) returns timestamp as
'select case if $1 > $2 then $1 else $2' language sql;

(see here for OVERLAPS:
http://developer.postgresql.org/docs...-datetime.html )

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
www.google.com: interfaz de linea de comando para la web.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #5
>> Now we want to generate a report of the availability. But for a
specific application both servers must be up at the same time. <<

Chapter 13 in SQL FOR SMARTIES. Also see Rick Snodgrass's book on
temporal SQL queries. But first thing, use timestamps or this is
going to be stinking mess.
Nov 11 '05 #6
Alvaro Herrera Munoz <al******@dcc.uchile.cl> writes:
I'm not sure if the OVERLAPS operator works on released versions;


Yes, it's been there since 7.2 or so, though sadly lacking in
documentation :-(. But I don't see any obvious way that it helps
for this problem.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #7
On Tue, 9 Sep 2003, Alvaro Herrera Munoz wrote:
(see here for OVERLAPS:
http://developer.postgresql.org/docs...-datetime.html )


Overlap works great and simplifies the SQL. Nice.

--
PGP/GPG Key-ID:
http://blackhole.pca.dfn.de:11371/pk...rch=0xB5A1AFE1

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #8
On Tue, 9 Sep 2003, Alvaro Herrera Munoz wrote:
(see here for OVERLAPS:
http://developer.postgresql.org/docs...-datetime.html )


Overlap works great and simplifies the SQL. Nice.

--
PGP/GPG Key-ID:
http://blackhole.pca.dfn.de:11371/pk...rch=0xB5A1AFE1

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Phil Sandler | last post by:
All, I have a table with start and end dates/times in it, and would like to be able to calculate the number of hours represented, accounting for...
4
by: Holger Marzen | last post by:
Say, we have uptimes from several servers: Server up_from up_to ------ ------- ------- s1 0:00 8:00 s1 10:00 ...
6
by: Penguin | last post by:
At some long ago time Steve Jorgensen answered thus: Subject: Re: How can I round a time? Newsgroups: comp.databases.ms-access Date: 1998/12/11...
2
by: Adam | last post by:
If I have a long task to do in my timer event, how can I properlystop my timer again being notified again while I am still processing? Thanks. ...
3
by: Fahad Aijaz | last post by:
Can any one tell me how can I create overlapping images using ASP.NET. That is, One rectangle drawn as a background image and then another image is...
8
by: Rsapru | last post by:
i have a table containing following data effdate termdate uid ----------- ----------- ----------- 1 2 1 3 4 ...
4
by: gzaxar | last post by:
Hi to all in forum. It is my first post here. I am quite new in MsAccess programming. Here is a problem which i am facing to. I want to keep...
2
by: Rombolt | last post by:
Hi I have a MSSQL table with many time intervals stored as datetime. Each time interval is also assigned a numeric type that specifies what type of...
7
by: Breal | last post by:
I have a list that looks like the following I would like to be able to determine which of these overlap each other. So, in this case, tuple 1...
15
by: student4lifer | last post by:
Hello, I have 2 time fields dynamically generated in format "m/d/y H:m". Could someone show me a good function to calculate the time interval...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.