pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: chinnaobi <chinnaobi@gmail.com>
To: pgsql-sql@postgresql.org
Subject: SQL query with Overlapping date time ranges
Date: Thu, 18 Apr 2013 20:41:06 -0700 (PDT)
Message-ID: <1366342866770-5752610.post@n5.nabble.com> (raw )
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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-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.
Message-ID: <1366342866770-5752610.post@n5.nabble.com>
Permalink: ../1366342866770-5752610.post@n5.nabble.com/
Also on: postgresql.org/message-id/1366342866770-5752610.post@n5.nabble.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: chinnaobi@gmail.com
Subject: Re: SQL query with Overlapping date time ranges
In-Reply-To: <1366342866770-5752610.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox