SQL query with Overlapping date time ranges

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 both servers are down at the same time or Zone1
is down.Please use the below sample data zonename, nodeid, nodelabel,
noderegainedservice, nodelostserviceZone1 27 Srv1 2013-02-21 10:04:56+08
2013-02-21 09:48:48+08Zone1 27 Srv1 2013-02-21 10:14:01+08 2013-02-21
10:09:27+08Zone1 27 Srv1 2013-02-21 10:26:29+08 2013-02-21 10:24:20+08Zone1
27 Srv1 2013-02-21 11:27:24+08 2013-02-21 11:25:15+08Zone1 27 Srv1
2013-02-28 16:24:59+08 2013-02-28 15:52:59+08Zone1 27 Srv1 2013-02-28
16:56:19+08 2013-02-28 16:40:18+08Zone1 27 Srv1 2013-02-28 17:09:28+08
2013-02-28 16:58:38+08Zone1 27 Srv1 2013-02-28 17:39:50+08 2013-02-28
17:29:47+08Zone1 27 Srv1 2013-03-01 09:39:36+08 2013-02-28 19:12:26+08Zone1
27 Srv1 2013-03-01 13:35:07+08 2013-03-01 12:10:03+08Zone1 27 Srv1
2013-03-04 11:04:14+08 2013-03-04 10:48:07+08Zone1 27 Srv1 2013-03-06
16:36:56+08 2013-03-06 16:33:10+08Zone1 27 Srv1 2013-03-13 13:54:11+08
2013-03-06 16:43:51+08Zone1 27 Srv1 2013-03-14 11:43:28+08 2013-03-13
19:09:31+08Zone1 27 Srv1 2013-03-18 18:38:16+08 2013-03-15 18:55:31+08Zone1
27 Srv1 2013-03-22 11:18:57+08 2013-03-22 09:53:38+08Zone1 27 Srv1
2013-03-28 16:48:27+08 2013-03-26 10:23:47+08Zone1 27 Srv1 2013-04-04
10:33:24+08 2013-04-04 10:32:51+08Zone1 27 Srv1 2013-04-04 11:48:54+08
2013-04-04 11:48:23+08Zone1 27 Srv1 2013-04-08 19:01:34+08 2013-04-08
19:01:03+08Zone1 27 Srv1 2013-04-08 19:37:05+08 2013-04-08 19:31:38+08Zone1
27 Srv1 2013-04-08 21:48:07+08 2013-04-08 21:47:35+08Zone1 27 Srv1
2013-04-08 21:54:02+08 2013-04-08 21:52:29+08Zone1 27 Srv1 2013-04-10
09:33:53+08 2013-04-10 09:32:34+08Zone1 27 Srv1 2013-04-10 12:01:01+08
2013-04-10 12:00:30+08Zone1 27 Srv1 2013-04-10 14:57:25+08 2013-04-10
14:56:53+08Zone1 27 Srv1 2013-04-10 16:25:50+08 2013-04-10 16:24:31+08Zone1
27 Srv1 2013-04-10 16:57:02+08 2013-04-10 16:56:19+08Zone1 27 Srv1
2013-04-10 17:17:37+08 2013-04-10 17:15:18+08Zone1 27 Srv1 2013-04-11
21:35:43+08 2013-04-11 21:31:50+08Zone1 39 Srv2 2013-04-05 13:15:53+08
2013-04-05 12:26:04+08Zone1 39 Srv2 2013-04-05 13:23:10+08 2013-04-05
13:21:14+08Zone1 39 Srv2 2013-04-05 13:35:23+08 2013-04-05 13:33:32+08Zone1
39 Srv2 2013-04-05 15:17:25+08 2013-04-05 14:25:51+08Zone1 39 Srv2
2013-04-07 16:49:56+08 2013-04-05 17:43:01+08Zone1 39 Srv2 2013-04-09
22:32:19+08 2013-04-07 20:00:44+08Zone1 39 Srv2 2013-04-09 22:38:02+08
2013-04-09 22:37:40+08Zone1 39 Srv2 2013-04-10 11:16:21+08 2013-04-10
11:13:32+08Zone1 39 Srv2 2013-04-10 16:15:37+08 2013-04-10 15:44:05+08Zone1
39 Srv2 2013-04-10 16:23:07+08 2013-04-10 16:20:59+08Zone1 39 Srv2
2013-04-10 16:48:46+08 2013-04-10 16:33:29+08Zone1 39 Srv2 2013-04-10
17:19:11+08 2013-04-10 17:04:10+08Zone1 39 Srv2 2013-04-11 21:39:21+08
2013-04-11 21:28:51+08Zone1 39 Srv2 2013-04-11 22:05:02+08 2013-04-11
21:49:44+08Zone1 39 Srv2 2013-04-15 14:02:11+08 2013-04-12 16:41:48+08Zone1
39 Srv2 2013-04-17 00:00:00+08 2013-04-15 20:50:40+08Zone1 29 Srv3
2013-03-12 17:20:02+08 2013-03-12 17:16:49+08Zone1 29 Srv3 2013-03-12
18:08:30+08 2013-03-12 17:55:43+08Zone1 13 Srv4 2013-01-09 17:23:59+08
2013-01-09 17:19:13+08Zone1 13 Srv4 2013-01-10 16:54:27+08 2013-01-10
16:53:48+08Zone1 13 Srv4 2013-01-10 16:59:55+08 2013-01-10 16:56:56+08Zone1
13 Srv4 2013-01-10 17:07:10+08 2013-01-10 17:04:11+08Zone1 13 Srv4
2013-01-10 17:13:54+08 2013-01-10 17:10:42+08Zone1 13 Srv4 2013-01-16
10:31:45+08 2013-01-15 14:47:25+08Zone1 13 Srv4 2013-01-24 17:52:35+08
2013-01-24 17:20:31+08Zone1 13 Srv4 2013-01-28 17:24:25+08 2013-01-28
16:53:10+08Zone1 13 Srv4 2013-02-18 12:16:45+08 2013-02-18 12:10:05+08Zone1
13 Srv4 2013-02-18 15:00:26+08 2013-02-18 14:12:04+08Zone1 13 Srv4
2013-02-18 17:11:10+08 2013-02-18 17:00:58+08Zone1 13 Srv4 2013-02-21
10:14:24+08 2013-02-21 10:13:45+08Zone1 13 Srv4 2013-02-25 14:29:39+08
2013-02-25 13:44:50+08Zone1 13 Srv4 2013-02-26 10:40:08+08 2013-02-26
10:19:33+08Zone1 13 Srv4 2013-03-04 11:37:34+08 2013-03-04 11:00:56+08Zone1
13 Srv4 2013-04-10 16:25:27+08 2013-04-10 16:24:07+08Zone1 13 Srv4
2013-04-10 17:17:39+08 2013-04-10 17:14:40+08Zone1 13 Srv4 2013-04-11
21:39:05+08 2013-04-11 21:28:22+08

--
View this message in context: http://postgresql.1045698.n5.nabble.com/SQL-query-with-Overlapping-date-time-ranges-tp5752610.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.