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

Post a Comment

Popular posts from this blog

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

running rails generate controller foo home results in: class foocontroller < application controller def home end end (there's nothing on line in actual file, blank line) is there purpose blank line? it's common use newline indicate end of line, last line. many editors (like vi, use) add in newline silently. consensus text files (especially in unix world) should end in newline, , there have been problems historically if wasn't present. why should text files end newline? the tool used count lines in file "wc" counts newlines in file, without trailing newline show 3 instead of 4. it improves readability of templates used in generator. consider: https://github.com/rails/rails/blob/master/railties/lib/rails/generators/rails/controller/templates/controller.rb to remove trailing newline, template have last line of: end<% end -%> instead of: end <% end -%> that seems less readable me. the discussion below refers
Read more

keyboard - Smiles and long press feature in Android -

i've created android keyboard. how add features inserting smilies, , using long keypresses display symbols? to create long-press behavior allows select several options pop-up menu, follow guide (specifically pop-up menu section): http://developer.android.com/guide/topics/ui/menus.html smileys (also known emoticons) combination of keys, understood programs , parsed same way. have do, when clicks smiley face on keyboard, simulate pressing of key combination matching emoticon. example, when clicks smiley, should simulate key presses ':' , ')' :). here's list of emoticons: http://en.wikipedia.org/wiki/list_of_emoticons
Read more

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

i'm using express 3.0 , node v0.11.0. have button submits form , i'm using ajax post data json object node server. client code is: $('#contact').submit(function(e) { console.log('submit called'); var formdata = json.stringify($('form').serializeobject()); console.log(formdata); $.ajax({ url: "http://localhost:3000/savedata/", type: "post", datatype: 'json', data: {objectdata: formdata}, contenttype: "application/json", complete: function() { console.log('process complete'); }, success: function(data) { console.log('process sucess'); }, error: function() { console.log('process error'); }, }); return false; }); then on server: var express = require('express'); var app = express(); app.set('views', __dirname + '/views'); app.set('view engine', 'jade'); app.use(express.bodyparser());
Read more