Potsgresql Query with overlapping dates, multiple arrays

Question

Edit: I have edited this question to make more understandable. Excuse me for any misunderstandings.

I have a temporary table with columns

zone_name, nodeid, nodelabel, nodegainedservice, nodelostservice
Zone1, 3, Windows-SRV1, "2012-11-27 13:10:30+08", "2012-11-27 13:00:40+08"
Zone1, 5, Windows-SRV2, "2012-12-20 13:10:30+08", "2012-12-18 13:00:40+08"
....
....

Many zones and many nodes and same nodes with gained service and lost service many times.

nodegainedservice meaning node has come alive and nodelostservice meaning node has gone down.

How could I make a query to fetch each zone availability in a period?

e.g., Zone1 have Windows-SRV1, Windows-SRV2. Find how many times and how long Zone1 is down. These servers are replication servers, zone goes down when all the servers in the zone are down at some time and comes up if any of them comes alive.

Please use the below sample data

zonename nodeid  nodelabel  noderegainedservice  nodelostservice
Zone1  27  Windows-SRV1  2013-02-21 10:04:56+08  2013-02-21 09:48:48+08
Zone1  27  Windows-SRV1  2013-02-21 10:14:01+08  2013-02-21 10:09:27+08
Zone1  27  Windows-SRV1  2013-02-22 10:26:29+08  2013-02-22 10:24:20+08
Zone1  27  Windows-SRV1  2013-02-22 11:27:24+08  2013-02-22 11:25:15+08
Zone1  27  Windows-SRV1  2013-02-28 16:24:59+08  2013-02-28 15:52:59+08
Zone1  27  Windows-SRV1  2013-02-28 16:56:19+08  2013-02-28 16:40:18+08
Zone1  39  Windows-SRV2  2013-02-21 13:15:53+08  2013-02-21 12:26:04+08
Zone1  39  Windows-SRV2  2013-02-23 13:23:10+08  2013-02-22 10:21:14+08
Zone1  39  Windows-SRV2  2013-02-24 13:35:23+08  2013-02-23 13:33:32+08
Zone1  39  Windows-SRV2  2013-02-26 15:17:25+08  2013-02-25 14:25:51+08
Zone1  39  Windows-SRV2  2013-02-28 18:49:56+08  2013-02-28 15:43:01+08
Zone1  13  Windows-SRV3  2013-02-22 17:23:59+08  2013-02-22 10:19:13+08
Zone1  13  Windows-SRV3  2013-02-28 16:54:27+08  2013-02-28 16:13:48+08

Output zone_outages as follows e.g.,

zonename duration from_time to_time

zone1 00:02:09 2013-02-22 10:24:20+08 2013-02-22 10:26:29+08 
zone1 00:02:09 2013-02-22 11:25:15+08 2013-02-22 11:27:24+08    
zone1 00:11:11 2013-02-28 16:13:48+08 2013-02-28 16:24:59+08 
zone1 00:14:09 2013-02-28 16:40:18+08 2013-02-28 16:54:27+08

Note: There could be entries like this

Zone2  24  Windows-SRV12  \n  \n

In this case Zone2 Windows-SRV12 has never gone down and Zone2 availability will be 100%.

Have you tried anything yet? Also, please provide some sample data and expected output to get a better picture of what you want to do. — PinnyM
– PinnyM, Commented Apr 18, 2013 at 18:18
I think I can see what you're trying to do, but you'll need to provide a solid sample data set that covers all the cases - no servers down at a given time range, one server down, both servers down, servers going up and down at the exact same time, servers going up and down at quite different but overlapping times, servers going up and down at totally non-overlapping times. Expected output would indeed be very helpful. I think I'd solve this by translating each server's history into a series of downtime ranges then do range overlap tests. — Craig Ringer
– Craig Ringer, Commented Apr 19, 2013 at 1:07
@PinnyM Can this be done by the aggregation and time window functions ?? to find the overlaps from one row to other ?? — Reddy
– Reddy, Commented Apr 25, 2013 at 7:25
Why does the desired output not show Zone1 down on 2013-02-21? It appears to have been down from 09:48 to 10:04. (First row of sample data.) — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Apr 25, 2013 at 10:51
@MikeSherrill'Catcall' Hey I have edit the question to make it clear. Please have a look at it again.. Sorry.. — Reddy
– Reddy, Commented Apr 25, 2013 at 11:35

Denis de Bernardy · Accepted Answer · 2013-04-25 16:59:55Z

Have you considered PG 9.2's range type instead of two separate timestamp fields?

http://www.postgresql.org/docs/9.2/static/rangetypes.html

Something like:

CREATE TABLE availability (
    zone_name varchar, nodeid int, nodelabel varchar, during tsrange
);

INSERT INTO availability
VALUES (zone1, 3, 'srv1', '[2013-01-01 14:30, 2013-01-01 15:30)');

Unless I'm mistaking, you'd then be able to work with unions, intersections and such, which should make your work simpler. There are likely a few aggregate functions I'm unfamiliar with that cater to the latter, too.

If needed, additionally look into with statements and window functions for more complex queries:

http://www.postgresql.org/docs/9.2/static/tutorial-window.html

http://www.postgresql.org/docs/9.2/static/functions-window.html

Some testing reveals that sum() doesn't work with tsrange types.

That being said, the sql schema used in the follow-up queries:

drop table if exists nodes;

create table nodes (
    zone int not null,
    node int not null,
    uptime tsrange
);

-- this requires the btree_gist extension:
-- alter table nodes add exclude using gist (uptime with &&, zone with =, node with =);

The data (slight variation from your sample):

insert into nodes values
    (1, 1,  '[2013-02-20 00:00:00, 2013-02-21 09:40:00)'),
    (1, 1,  '[2013-02-21 09:48:48, 2013-02-21 10:04:56)'),
    (1, 1,  '[2013-02-21 10:09:27, 2013-02-21 10:14:01)'),
    (1, 1,  '[2013-02-22 10:24:20, 2013-02-22 10:26:29)'),
    (1, 1,  '[2013-02-22 11:25:15, 2013-02-22 11:27:24)'),
    (1, 1,  '[2013-02-28 15:52:59, 2013-02-28 16:24:59)'),
    (1, 1,  '[2013-02-28 16:40:18, 2013-02-28 16:56:19)'),
    (1, 1,  '[2013-02-28 17:00:00, infinity)'),
    (1, 2,  '[2013-02-20 00:00:01, 2013-02-21 12:15:00)'),
    (1, 2,  '[2013-02-21 12:26:04, 2013-02-21 13:15:53)'),
    (1, 2,  '[2013-02-22 10:21:14, 2013-02-23 13:23:10)'),
    (1, 2,  '[2013-02-23 13:33:32, 2013-02-24 13:35:23)'),
    (1, 2,  '[2013-02-25 14:25:51, 2013-02-26 15:17:25)'),
    (1, 2,  '[2013-02-28 15:43:01, 2013-02-28 18:49:56)'),
    (2, 3,  '[2013-02-20 00:00:01, 2013-02-22 09:01:00)'),
    (2, 3,  '[2013-02-22 10:19:13, 2013-02-22 17:23:59)'),
    (2, 3,  '[2013-02-28 16:13:48, 2013-02-28 16:54:27)');

Raw data in order (for clarity):

select *
from nodes
order by zone, uptime, node;

Yields:

 zone | node |                    uptime                     
------+------+-----------------------------------------------
    1 |    1 | ["2013-02-20 00:00:00","2013-02-21 09:40:00")
    1 |    2 | ["2013-02-20 00:00:01","2013-02-21 12:15:00")
    1 |    1 | ["2013-02-21 09:48:48","2013-02-21 10:04:56")
    1 |    1 | ["2013-02-21 10:09:27","2013-02-21 10:14:01")
    1 |    2 | ["2013-02-21 12:26:04","2013-02-21 13:15:53")
    1 |    2 | ["2013-02-22 10:21:14","2013-02-23 13:23:10")
    1 |    1 | ["2013-02-22 10:24:20","2013-02-22 10:26:29")
    1 |    1 | ["2013-02-22 11:25:15","2013-02-22 11:27:24")
    1 |    2 | ["2013-02-23 13:33:32","2013-02-24 13:35:23")
    1 |    2 | ["2013-02-25 14:25:51","2013-02-26 15:17:25")
    1 |    2 | ["2013-02-28 15:43:01","2013-02-28 18:49:56")
    1 |    1 | ["2013-02-28 15:52:59","2013-02-28 16:24:59")
    1 |    1 | ["2013-02-28 16:40:18","2013-02-28 16:56:19")
    1 |    1 | ["2013-02-28 17:00:00",infinity)
    2 |    3 | ["2013-02-20 00:00:01","2013-02-22 09:01:00")
    2 |    3 | ["2013-02-22 10:19:13","2013-02-22 17:23:59")
    2 |    3 | ["2013-02-28 16:13:48","2013-02-28 16:54:27")
(17 rows)

Nodes available @ 2013-02-21 09:20:00:

with upnodes as (
select zone, node, uptime
from nodes
where '2013-02-21 09:20:00'::timestamp <@ uptime
)
select *
from upnodes
order by zone, uptime, node;

Yields:

 zone | node |                    uptime                     
------+------+-----------------------------------------------
    1 |    1 | ["2013-02-20 00:00:00","2013-02-21 09:40:00")
    1 |    2 | ["2013-02-20 00:00:01","2013-02-21 12:15:00")
    2 |    3 | ["2013-02-20 00:00:01","2013-02-22 09:01:00")
(3 rows)

Nodes available from 2013-02-21 00:00:00 incl to 2013-02-24 00:00:00 excl:

with upnodes as (
select zone, node, uptime
from nodes
where '[2013-02-21 00:00:00, 2013-02-24 00:00:00)'::tsrange && uptime
)
select * from upnodes
order by zone, uptime, node;

Yields:

 zone | node |                    uptime                     
------+------+-----------------------------------------------
    1 |    1 | ["2013-02-20 00:00:00","2013-02-21 09:40:00")
    1 |    2 | ["2013-02-20 00:00:01","2013-02-21 12:15:00")
    1 |    1 | ["2013-02-21 09:48:48","2013-02-21 10:04:56")
    1 |    1 | ["2013-02-21 10:09:27","2013-02-21 10:14:01")
    1 |    2 | ["2013-02-21 12:26:04","2013-02-21 13:15:53")
    1 |    2 | ["2013-02-22 10:21:14","2013-02-23 13:23:10")
    1 |    1 | ["2013-02-22 10:24:20","2013-02-22 10:26:29")
    1 |    1 | ["2013-02-22 11:25:15","2013-02-22 11:27:24")
    1 |    2 | ["2013-02-23 13:33:32","2013-02-24 13:35:23")
    2 |    3 | ["2013-02-20 00:00:01","2013-02-22 09:01:00")
    2 |    3 | ["2013-02-22 10:19:13","2013-02-22 17:23:59")
(11 rows)

Zones available from 2013-02-21 00:00:00 incl to 2013-02-24 00:00:00 excl'

with upnodes as (
select zone, node, uptime
from nodes
where '[2013-02-21 00:00:00, 2013-02-24 00:00:00)'::tsrange && uptime
),
upzones_max as (
select u1.zone, tsrange(lower(u1.uptime), max(upper(u2.uptime))) as uptime
from upnodes as u1
join upnodes as u2 on u2.zone = u1.zone and u2.uptime && u1.uptime
group by u1.zone, lower(u1.uptime)
),
upzones as (
select u1.zone, tsrange(min(lower(u2.uptime)), upper(u1.uptime)) as uptime
from upzones_max as u1
join upzones_max as u2 on u2.zone = u1.zone and u2.uptime && u1.uptime
group by u1.zone, upper(u1.uptime)
)
select zone, uptime, upper(uptime) - lower(uptime) as duration
from upzones
order by zone, uptime;

Yields:

 zone |                    uptime                     |    duration     
------+-----------------------------------------------+-----------------
    1 | ["2013-02-20 00:00:00","2013-02-21 12:15:00") | 1 day 12:15:00
    1 | ["2013-02-21 12:26:04","2013-02-21 13:15:53") | 00:49:49
    1 | ["2013-02-22 10:21:14","2013-02-23 13:23:10") | 1 day 03:01:56
    1 | ["2013-02-23 13:33:32","2013-02-24 13:35:23") | 1 day 00:01:51
    2 | ["2013-02-20 00:00:01","2013-02-22 09:01:00") | 2 days 09:00:59
    2 | ["2013-02-22 10:19:13","2013-02-22 17:23:59") | 07:04:46
(6 rows)

There might be a better way to write the latter query if you write (or find) a custom aggregate function that sums overlapping range types -- the non-trivial issue that I ran into was to isolate an adequate group by clause; I ended up settling with two nested group by clauses.

The queries could also be rewritten to accommodate your current schema, either by replacing the uptime field by an expression such as tsrange(start_date, end_date), or by writing a view that does so.

Thank you for the reply. If I run the same query with donwtime(start,end) tsrange and min(upper(u2.downtime)) in downzones_max and max(lower(u2.downtime)) in downzones, I will get the outages of the zone from start time to end time of outage in the zone irrespective of nodes in the zone. But I have to consider nodeid or nodelabel. Please have a look at this SQL Fiddle Zone_outages
Denis query works well for my case of finding zone_outages irrespective of nodeid. It only works on start times and end times. But here I have to consider nodeid since some node may not be down at the same outage in the zone. Some body can help with this ?? How could I proceed ..
@Reddy: If you add the node in the select and group by clauses in the last query I highlighted, you'll get the uptime per node.

Reddy · Accepted Answer · 2013-04-28 09:15:51Z

DROP table if exists temptable;
CREATE TABLE temptable
(
  zone_name character varying(255),
  nodeid integer,
  nodelabel character varying(255),
  nodegainedservice timestamp with time zone,
  nodelostservice timestamp with time zone
);
INSERT INTO tempTable (zone_name, nodeid, nodelabel, nodegainedservice, nodelostservice) VALUES 
('Zone1',   27, 'Windows-SRV1', '2013-02-21 10:04:56+08',   '2013-02-21 09:48:48+08'),
('Zone1',   27, 'Windows-SRV1', '2013-02-21 10:14:01+08',   '2013-02-21 10:09:27+08'),
('Zone1',   27, 'Windows-SRV1', '2013-02-22 10:26:29+08',   '2013-02-22 10:24:20+08'),
('Zone1',   27, 'Windows-SRV1', '2013-02-22 11:27:24+08',   '2013-02-22 11:25:15+08'),
('Zone1',   27, 'Windows-SRV1', '2013-02-28 16:24:59+08',   '2013-02-28 15:52:59+08'),
('Zone1',   27, 'Windows-SRV1', '2013-02-28 16:56:19+08',   '2013-02-28 16:40:18+08'),
('Zone1',   39, 'Windows-SRV2', '2013-02-21 13:15:53+08',   '2013-02-21 12:26:04+08'),
('Zone1',   39, 'Windows-SRV2', '2013-02-23 13:23:10+08',   '2013-02-22 10:21:14+08'),
('Zone1',   39, 'Windows-SRV2', '2013-02-24 13:35:23+08',   '2013-02-23 13:33:32+08'),
('Zone1',   39, 'Windows-SRV2', '2013-02-26 15:17:25+08',   '2013-02-25 14:25:51+08'),
('Zone1',   39, 'Windows-SRV2', '2013-02-28 18:49:56+08',   '2013-02-28 15:43:01+08'),
('Zone2',   13, 'Windows-SRV3', '2013-02-22 17:23:59+08',   '2013-02-22 10:19:13+08'),
('Zone2',   13, 'Windows-SRV3', '2013-02-28 16:54:27+08',   '2013-02-28 16:13:48+08'),
('Zone2',   14, 'Windows-SRV4', '2013-02-22 11:02:56+08',   '2013-02-22 10:01:48+08');

with downodes as (
select zone_name, nodeid, nodelostservice, nodegainedservice
from temptable
WHERE (nodelostservice, nodegainedservice) OVERLAPS ('Wed Feb 20 00:00:00 +0800 2013'::TIMESTAMP, 'Fri Mar 01 00:00:00 +0800 2013'::TIMESTAMP)
),
donezones_max as(
select downodes1.zone_name, downodes1.nodeid, downodes1.nodelostservice, min(downodes2.nodegainedservice) as nodegainedservice
from downodes as downodes1
join downodes as downodes2 on downodes2.zone_name = downodes1.zone_name and ((downodes2.nodelostservice, downodes2.nodegainedservice) OVERLAPS (downodes1.nodelostservice, downodes1.nodegainedservice))
group by downodes1.zone_name, downodes1.nodeid, downodes1.nodelostservice
),
downzones as(
select downodes1.zone_name, downodes1.nodeid, max(downodes2.nodelostservice) as nodelostservice, downodes1.nodegainedservice
from donezones_max as downodes1
join donezones_max as downodes2 on downodes2.zone_name = downodes1.zone_name and  ((downodes2.nodelostservice, downodes2.nodegainedservice) OVERLAPS (downodes1.nodelostservice, downodes1.nodegainedservice))
group by downodes1.zone_name, downodes1.nodeid, downodes1.nodegainedservice 
),
zone_outages as(
SELECT 
    zone_name,
    nodelostservice,
    nodegainedservice,
    nodegainedservice - nodelostservice AS duration,
    CAST('1' AS INTEGER) as outage_counter
FROM downzones GROUP BY zone_name, nodelostservice, nodegainedservice HAVING COUNT(*) > 1 ORDER BY zone_name, nodelostservice)
select 
    zone_name,
    EXTRACT(epoch from (SUM(duration) / (greatest(1, SUM(outage_counter))))) AS average_duration_seconds,
    SUM(outage_counter) AS outage_count
FROM zone_outages GROUP BY zone_name ORDER BY zone_name

Collectives™ on Stack Overflow

Potsgresql Query with overlapping dates, multiple arrays

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related