Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UT2CH-0005fl-L1 for pgsql-sql@arkaria.postgresql.org; Fri, 19 Apr 2013 03:41:13 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UT2CH-0003UX-5i for pgsql-sql@arkaria.postgresql.org; Fri, 19 Apr 2013 03:41:13 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UT2CG-0003US-4A for pgsql-sql@postgresql.org; Fri, 19 Apr 2013 03:41:12 +0000 Received: from sam.nabble.com ([216.139.236.26]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UT2CC-0004L8-IC for pgsql-sql@postgresql.org; Fri, 19 Apr 2013 03:41:11 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1UT2CA-0000TP-Ql for pgsql-sql@postgresql.org; Thu, 18 Apr 2013 20:41:06 -0700 Date: Thu, 18 Apr 2013 20:41:06 -0700 (PDT) From: chinnaobi To: pgsql-sql@postgresql.org Message-ID: <1366342866770-5752610.post@n5.nabble.com> Subject: SQL query with Overlapping date time ranges MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_24106_16900919.1366342866818" X-Pg-Spam-Score: 0.9 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ------=_Part_24106_16900919.1366342866818 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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. ------=_Part_24106_16900919.1366342866818 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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, nodelostservice Zone1 27 Srv1 2013-02-21 10:04:56+08 2013-02-21 09:48:48+08 Zone1 27 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 Zone1 27 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 Zone1 27 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 Zone1 27 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 Zone1 27 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 Zone1 27 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 Zone1 27 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 Zone1 27 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 Zone1 27 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 Zone1 27 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 Zone1 27 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 Zone1 27 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 Zone1 27 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 Zone1 27 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 Zone1 27 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 Zone1 39 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 Zone1 39 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 Zone1 39 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 Zone1 39 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 Zone1 39 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 Zone1 39 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 Zone1 39 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 Zone1 39 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 Zone1 29 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 Zone1 13 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 Zone1 13 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 Zone1 13 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 Zone1 13 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 Zone1 13 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 Zone1 13 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 Zone1 13 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 Zone1 13 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 Zone1 13 Srv4 2013-04-11 21:39:05+08 2013-04-11 21:28:22+08
View this message in context: SQL query with Overlapping date time ranges
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
------=_Part_24106_16900919.1366342866818--