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.
