#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    1
    Rep Power
    0

    SQL Query time overlap Multiple Arrays


    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 Zone1 is down. These servers are replication servers, zone goes down when all the servers in the zone are down at some time and comes up if any of them comes alive.

    Please use the below sample data

    zonename nodeid nodelabel noderegainedservice nodelostservice
    Zone1 27 Windows-SRV1 2013-02-21 10:04:56+08 2013-02-21 09:48:48+08
    Zone1 27 Windows-SRV1 2013-02-21 10:14:01+08 2013-02-21 10:09:27+08
    Zone1 27 Windows-SRV1 2013-02-22 10:26:29+08 2013-02-22 10:24:20+08
    Zone1 27 Windows-SRV1 2013-02-22 11:27:24+08 2013-02-22 11:25:15+08
    Zone1 27 Windows-SRV1 2013-02-28 16:24:59+08 2013-02-28 15:52:59+08
    Zone1 27 Windows-SRV1 2013-02-28 16:56:19+08 2013-02-28 16:40:18+08
    Zone1 39 Windows-SRV2 2013-02-21 13:15:53+08 2013-02-21 12:26:04+08
    Zone1 39 Windows-SRV2 2013-02-23 13:23:10+08 2013-02-22 10:21:14+08
    Zone1 39 Windows-SRV2 2013-02-24 13:35:23+08 2013-02-23 13:33:32+08
    Zone1 39 Windows-SRV2 2013-02-26 15:17:25+08 2013-02-25 14:25:51+08
    Zone1 39 Windows-SRV2 2013-02-28 18:49:56+08 2013-02-28 15:43:01+08
    Zone1 13 Windows-SRV3 2013-02-22 17:23:59+08 2013-02-22 10:19:13+08
    Zone1 13 Windows-SRV3 2013-02-28 16:54:27+08 2013-02-28 16:13:48+08
    Output zone_outages as follows e.g.,

    zonename duration from_time to_time

    zone1 00:02:09 2013-02-22 10:24:20+08 2013-02-22 10:26:29+08
    zone1 00:02:09 2013-02-22 11:25:15+08 2013-02-22 11:27:24+08
    zone1 00:11:11 2013-02-28 16:13:48+08 2013-02-28 16:24:59+08
    zone1 00:14:09 2013-02-28 16:40:18+08 2013-02-28 16:54:27+08
    Note: There could be entries like this

    Zone2 24 Windows-SRV12 \n \n
    In this case Zone2 Windows-SRV12 has never gone down and Zone2 availability will be 100%.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    What's wrong with the answer you got here: http://stackoverflow.com/a/16213891/330315
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo