SQL query with Overlapping date time ranges - Mailing list pgsql-sql

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-1813: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
makea query to fetch each zone availability in a period? e.g., Zone1 have Windows-SRV1, Windows-SRV2. Find how many
timesand how long both servers are down at the same time or Zone1 is down. Please use the below sample data zonename,
nodeid,nodelabel, noderegainedservice, nodelostservice Zone1 27 Srv1 2013-02-21 10:04:56+08 2013-02-21 09:48:48+08
Zone127 Srv1 2013-02-21 10:14:01+08 2013-02-21 10:09:27+08 Zone1 27 Srv1 2013-02-21 10:26:29+08 2013-02-21 10:24:20+08
Zone127 Srv1 2013-02-21 11:27:24+08 2013-02-21 11:25:15+08 Zone1 27 Srv1 2013-02-28 16:24:59+08 2013-02-28 15:52:59+08
Zone127 Srv1 2013-02-28 16:56:19+08 2013-02-28 16:40:18+08 Zone1 27 Srv1 2013-02-28 17:09:28+08 2013-02-28 16:58:38+08
Zone127 Srv1 2013-02-28 17:39:50+08 2013-02-28 17:29:47+08 Zone1 27 Srv1 2013-03-01 09:39:36+08 2013-02-28 19:12:26+08
Zone127 Srv1 2013-03-01 13:35:07+08 2013-03-01 12:10:03+08 Zone1 27 Srv1 2013-03-04 11:04:14+08 2013-03-04 10:48:07+08
Zone127 Srv1 2013-03-06 16:36:56+08 2013-03-06 16:33:10+08 Zone1 27 Srv1 2013-03-13 13:54:11+08 2013-03-06 16:43:51+08
Zone127 Srv1 2013-03-14 11:43:28+08 2013-03-13 19:09:31+08 Zone1 27 Srv1 2013-03-18 18:38:16+08 2013-03-15 18:55:31+08
Zone127 Srv1 2013-03-22 11:18:57+08 2013-03-22 09:53:38+08 Zone1 27 Srv1 2013-03-28 16:48:27+08 2013-03-26 10:23:47+08
Zone127 Srv1 2013-04-04 10:33:24+08 2013-04-04 10:32:51+08 Zone1 27 Srv1 2013-04-04 11:48:54+08 2013-04-04 11:48:23+08
Zone127 Srv1 2013-04-08 19:01:34+08 2013-04-08 19:01:03+08 Zone1 27 Srv1 2013-04-08 19:37:05+08 2013-04-08 19:31:38+08
Zone127 Srv1 2013-04-08 21:48:07+08 2013-04-08 21:47:35+08 Zone1 27 Srv1 2013-04-08 21:54:02+08 2013-04-08 21:52:29+08
Zone127 Srv1 2013-04-10 09:33:53+08 2013-04-10 09:32:34+08 Zone1 27 Srv1 2013-04-10 12:01:01+08 2013-04-10 12:00:30+08
Zone127 Srv1 2013-04-10 14:57:25+08 2013-04-10 14:56:53+08 Zone1 27 Srv1 2013-04-10 16:25:50+08 2013-04-10 16:24:31+08
Zone127 Srv1 2013-04-10 16:57:02+08 2013-04-10 16:56:19+08 Zone1 27 Srv1 2013-04-10 17:17:37+08 2013-04-10 17:15:18+08
Zone127 Srv1 2013-04-11 21:35:43+08 2013-04-11 21:31:50+08 Zone1 39 Srv2 2013-04-05 13:15:53+08 2013-04-05 12:26:04+08
Zone139 Srv2 2013-04-05 13:23:10+08 2013-04-05 13:21:14+08 Zone1 39 Srv2 2013-04-05 13:35:23+08 2013-04-05 13:33:32+08
Zone139 Srv2 2013-04-05 15:17:25+08 2013-04-05 14:25:51+08 Zone1 39 Srv2 2013-04-07 16:49:56+08 2013-04-05 17:43:01+08
Zone139 Srv2 2013-04-09 22:32:19+08 2013-04-07 20:00:44+08 Zone1 39 Srv2 2013-04-09 22:38:02+08 2013-04-09 22:37:40+08
Zone139 Srv2 2013-04-10 11:16:21+08 2013-04-10 11:13:32+08 Zone1 39 Srv2 2013-04-10 16:15:37+08 2013-04-10 15:44:05+08
Zone139 Srv2 2013-04-10 16:23:07+08 2013-04-10 16:20:59+08 Zone1 39 Srv2 2013-04-10 16:48:46+08 2013-04-10 16:33:29+08
Zone139 Srv2 2013-04-10 17:19:11+08 2013-04-10 17:04:10+08 Zone1 39 Srv2 2013-04-11 21:39:21+08 2013-04-11 21:28:51+08
Zone139 Srv2 2013-04-11 22:05:02+08 2013-04-11 21:49:44+08 Zone1 39 Srv2 2013-04-15 14:02:11+08 2013-04-12 16:41:48+08
Zone139 Srv2 2013-04-17 00:00:00+08 2013-04-15 20:50:40+08 Zone1 29 Srv3 2013-03-12 17:20:02+08 2013-03-12 17:16:49+08
Zone129 Srv3 2013-03-12 18:08:30+08 2013-03-12 17:55:43+08 Zone1 13 Srv4 2013-01-09 17:23:59+08 2013-01-09 17:19:13+08
Zone113 Srv4 2013-01-10 16:54:27+08 2013-01-10 16:53:48+08 Zone1 13 Srv4 2013-01-10 16:59:55+08 2013-01-10 16:56:56+08
Zone113 Srv4 2013-01-10 17:07:10+08 2013-01-10 17:04:11+08 Zone1 13 Srv4 2013-01-10 17:13:54+08 2013-01-10 17:10:42+08
Zone113 Srv4 2013-01-16 10:31:45+08 2013-01-15 14:47:25+08 Zone1 13 Srv4 2013-01-24 17:52:35+08 2013-01-24 17:20:31+08
Zone113 Srv4 2013-01-28 17:24:25+08 2013-01-28 16:53:10+08 Zone1 13 Srv4 2013-02-18 12:16:45+08 2013-02-18 12:10:05+08
Zone113 Srv4 2013-02-18 15:00:26+08 2013-02-18 14:12:04+08 Zone1 13 Srv4 2013-02-18 17:11:10+08 2013-02-18 17:00:58+08
Zone113 Srv4 2013-02-21 10:14:24+08 2013-02-21 10:13:45+08 Zone1 13 Srv4 2013-02-25 14:29:39+08 2013-02-25 13:44:50+08
Zone113 Srv4 2013-02-26 10:40:08+08 2013-02-26 10:19:33+08 Zone1 13 Srv4 2013-03-04 11:37:34+08 2013-03-04 11:00:56+08
Zone113 Srv4 2013-04-10 16:25:27+08 2013-04-10 16:24:07+08 Zone1 13 Srv4 2013-04-10 17:17:39+08 2013-04-10 17:14:40+08
Zone113 Srv4 2013-04-11 21:39:05+08 2013-04-11 21:28:22+08 <br /><hr align="left" width="300" /> View this message in
context:<a
href="http://postgresql.1045698.n5.nabble.com/SQL-query-with-Overlapping-date-time-ranges-tp5752610.html">SQLquery with
Overlappingdate time ranges</a><br /> Sent from the <a
href="http://postgresql.1045698.n5.nabble.com/PostgreSQL-sql-f2142323.html">PostgreSQL- sql mailing list archive</a> at
Nabble.com.<br/>