sql - Potsgresql Query with overlapping dates, multiple arrays -


edit: have edited question make more understandable. excuse me misunderstandings.

i have temporary table 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 , many nodes , same nodes gained service , lost service many times.

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

how make query fetch each zone availability in period?

e.g., zone1 have windows-srv1, windows-srv2. find how many times , how long zone1 down. these servers replication servers, zone goes down when servers in zone down @ time , comes if of them comes alive.

please use 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 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 entries this

zone2  24  windows-srv12  \n  \n 

in case zone2 windows-srv12 has never gone down , zone2 availability 100%.

have considered pg 9.2's range type instead of 2 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 availability values (zone1, 3, 'srv1', '[2013-01-01 14:30, 2013-01-01 15:30)'); 

unless i'm mistaking, you'd able work unions, intersections , such, should make work simpler. there few aggregate functions i'm unfamiliar cater latter, too.

if needed, additionally statements , window functions 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 sum() doesn't work tsrange types.

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

drop table if exists nodes;  create table nodes (     zone int not null,     node int not null,     uptime tsrange );  -- requires btree_gist extension: -- alter table nodes add exclude using gist (uptime &&, zone =, node =); 

the data (slight variation sample):

insert 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 * nodes order 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 ( select zone, node, uptime nodes '2013-02-21 09:20:00'::timestamp <@ uptime ) select * upnodes order 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 2013-02-21 00:00:00 incl 2013-02-24 00:00:00 excl:

with upnodes ( select zone, node, uptime nodes '[2013-02-21 00:00:00, 2013-02-24 00:00:00)'::tsrange && uptime ) select * upnodes order 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 2013-02-21 00:00:00 incl 2013-02-24 00:00:00 excl'

with upnodes ( select zone, node, uptime nodes '[2013-02-21 00:00:00, 2013-02-24 00:00:00)'::tsrange && uptime ), upzones_max ( select u1.zone, tsrange(lower(u1.uptime), max(upper(u2.uptime))) uptime upnodes u1 join upnodes u2 on u2.zone = u1.zone , u2.uptime && u1.uptime group u1.zone, lower(u1.uptime) ), upzones ( select u1.zone, tsrange(min(lower(u2.uptime)), upper(u1.uptime)) uptime upzones_max u1 join upzones_max u2 on u2.zone = u1.zone , u2.uptime && u1.uptime group u1.zone, upper(u1.uptime) ) select zone, uptime, upper(uptime) - lower(uptime) duration upzones order 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 better way write latter query if write (or find) custom aggregate function sums overlapping range types -- non-trivial issue ran isolate adequate group clause; ended settling 2 nested group clauses.

the queries rewritten accommodate current schema, either replacing uptime field expression such tsrange(start_date, end_date), or writing view so.


Comments

Popular posts from this blog

Why does Ruby on Rails generate add a blank line to the end of a file? -

keyboard - Smiles and long press feature in Android -

node.js - Bad Request - node js ajax post -