Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2013
    Posts
    2

    Unanswered: SQL HELP with time window overlaps

    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
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    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://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

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

  3. #3
    Join Date
    Apr 2013
    Posts
    2
    I hope you read my comments on the same post.. The query there doesn't consider nodeid, it just consider start and end times. e.g;

    Zone1 has srv1, srv2,srv3 with down times start and end;
    Zone2 has srv3,srv4,srv5,srv6 with down times start and end;

    I have to calculate srv1 intersection srv2 intersection srv3 for zone1 each outage. If there is no downtime intersected in all node there will be no downtime in the zone.

    Denis query runs based on the dowtimes overlap -- first tier get's all the downtime ranges.
    2nd tier just ignores the nodeid's in the zone and then just calculates the fused downtimes there... I hope your understand.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here are two examples tested on DB2 9.7 for Windows.

    Though, there are some differences between DB2 and PostgreSQL
    (e.g. string representation of timestamp datatype, date/time calcurations, so on...),
    it might be not so difficult to adjust to syntax of PostgreSQL.

    Sample Data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      outages
    ( zonename , nodeid , nodelabel , noderegainedservice , nodelostservice ) AS (
    SELECT zonename , nodeid , nodelabel
         , TIMESTAMP(noderegainedservice)
         , TIMESTAMP(nodelostservice)
     FROM (VALUES
              ( 'Zone1' , 27 , 'Windows-SRV1' , '2013-02-21 10:04:56' , '2013-02-21 09:48:48' )
            , ( 'Zone1' , 27 , 'Windows-SRV1' , '2013-02-21 10:14:01' , '2013-02-21 10:09:27' )
            , ( 'Zone1' , 39 , 'Windows-SRV2' , '2013-02-21 13:15:53' , '2013-02-21 12:26:04' )
            , ( 'Zone1' , 27 , 'Windows-SRV1' , '2013-02-22 10:26:29' , '2013-02-22 10:24:20' )
            , ( 'Zone1' , 27 , 'Windows-SRV1' , '2013-02-22 11:27:24' , '2013-02-22 11:25:15' )
            , ( 'Zone1' , 13 , 'Windows-SRV3' , '2013-02-22 17:23:59' , '2013-02-22 10:19:13' )
            , ( 'Zone1' , 39 , 'Windows-SRV2' , '2013-02-23 13:23:10' , '2013-02-22 10:21:14' )
            , ( 'Zone1' , 39 , 'Windows-SRV2' , '2013-02-24 13:35:23' , '2013-02-23 13:33:32' )
            , ( 'Zone1' , 39 , 'Windows-SRV2' , '2013-02-26 15:17:25' , '2013-02-25 14:25:51' )
            , ( 'Zone1' , 27 , 'Windows-SRV1' , '2013-02-28 16:24:59' , '2013-02-28 15:52:59' )
            , ( 'Zone1' , 13 , 'Windows-SRV3' , '2013-02-28 16:54:27' , '2013-02-28 16:13:48' )
            , ( 'Zone1' , 27 , 'Windows-SRV1' , '2013-02-28 16:56:19' , '2013-02-28 16:40:18' )
            , ( 'Zone1' , 39 , 'Windows-SRV2' , '2013-02-28 18:49:56' , '2013-02-28 15:43:01' )
          ) AS t( zonename , nodeid , nodelabel , noderegainedservice , nodelostservice )
    )
    SELECT * FROM outages;
    ------------------------------------------------------------------------------
    
    ZONENAME NODEID      NODELABEL    NODEREGAINEDSERVICE        NODELOSTSERVICE           
    -------- ----------- ------------ -------------------------- --------------------------
    Zone1             27 Windows-SRV1 2013-02-21-10.04.56.000000 2013-02-21-09.48.48.000000
    Zone1             27 Windows-SRV1 2013-02-21-10.14.01.000000 2013-02-21-10.09.27.000000
    Zone1             39 Windows-SRV2 2013-02-21-13.15.53.000000 2013-02-21-12.26.04.000000
    Zone1             27 Windows-SRV1 2013-02-22-10.26.29.000000 2013-02-22-10.24.20.000000
    Zone1             27 Windows-SRV1 2013-02-22-11.27.24.000000 2013-02-22-11.25.15.000000
    Zone1             13 Windows-SRV3 2013-02-22-17.23.59.000000 2013-02-22-10.19.13.000000
    Zone1             39 Windows-SRV2 2013-02-23-13.23.10.000000 2013-02-22-10.21.14.000000
    Zone1             39 Windows-SRV2 2013-02-24-13.35.23.000000 2013-02-23-13.33.32.000000
    Zone1             39 Windows-SRV2 2013-02-26-15.17.25.000000 2013-02-25-14.25.51.000000
    Zone1             27 Windows-SRV1 2013-02-28-16.24.59.000000 2013-02-28-15.52.59.000000
    Zone1             13 Windows-SRV3 2013-02-28-16.54.27.000000 2013-02-28-16.13.48.000000
    Zone1             27 Windows-SRV1 2013-02-28-16.56.19.000000 2013-02-28-16.40.18.000000
    Zone1             39 Windows-SRV2 2013-02-28-18.49.56.000000 2013-02-28-15.43.01.000000
    
      13 record(s) selected.
    Example 1:
    Code:
    SELECT s.zonename
         , TRANSLATE(
              'ij:kl:mn'
            , DIGITS( min_regained - MAX(nodelostservice) )
            , 'abcdefghijklmn'
           ) AS duration
         , MAX(nodelostservice) AS from_time
         , min_regained         AS to_time
     FROM  (SELECT DISTINCT
                   zonename
                 , (SELECT MIN(noderegainedservice)
                     FROM  outages AS regn
                     WHERE regn.zonename            = lost2.zonename
                       AND regn.noderegainedservice > lost2.nodelostservice
                   ) AS min_regained
             FROM  outages AS lost2
           ) AS s
     INNER JOIN
           outages AS lost
      ON   s.zonename = lost.zonename
       AND s.min_regained
           BETWEEN lost.nodelostservice
               AND lost.noderegainedservice
     GROUP BY
           s.zonename
         , s.min_regained
     HAVING
           COUNT(*)
         = (SELECT COUNT(DISTINCT nodeid)
             FROM  outages z
             WHERE z.zonename = s.zonename
           )
     ORDER BY
           zonename
         , from_time
         , to_time
    ;
    ------------------------------------------------------------------------------
    
    ZONENAME DURATION FROM_TIME                  TO_TIME                   
    -------- -------- -------------------------- --------------------------
    Zone1    00:02:09 2013-02-22-10.24.20.000000 2013-02-22-10.26.29.000000
    Zone1    00:02:09 2013-02-22-11.25.15.000000 2013-02-22-11.27.24.000000
    Zone1    00:11:11 2013-02-28-16.13.48.000000 2013-02-28-16.24.59.000000
    Zone1    00:14:09 2013-02-28-16.40.18.000000 2013-02-28-16.54.27.000000
    
      4 record(s) selected.

    Example 2:
    Code:
    SELECT s.zonename
         , TRANSLATE(
              'ij:kl:mn'
            , DIGITS(min_regained - max_lost)
            , 'abcdefghijklmn'
           ) AS duration
         , CHAR(TO_CHAR(max_lost     , 'yyyy-mm-dd hh24:mi:ss') , 19) AS from_time
         , CHAR(TO_CHAR(min_regained , 'yyyy-mm-dd hh24:mi:ss') , 19) AS to_time
     FROM  (SELECT zonename
                 , min_regained
                 , MAX(nodelostservice) AS max_lost
             FROM  (SELECT zonename
                         , nodelostservice
                         , (SELECT MIN(noderegainedservice)
                             FROM  outages AS regn
                             WHERE regn.zonename            = lost.zonename
                               AND regn.noderegainedservice > lost.nodelostservice
                           ) AS min_regained
                     FROM  outages AS lost
                   ) AS t
             GROUP BY
                   zonename
                 , min_regained
           ) AS s
     WHERE (SELECT COUNT(*)
             FROM  outages AS lost2
             WHERE lost2.zonename = s.zonename
               AND lost2.noderegainedservice >= s.min_regained
               AND lost2.nodelostservice     <= s.max_lost
           )
         = (SELECT COUNT(DISTINCT nodeid)
             FROM  outages z
             WHERE z.zonename = s.zonename
           )
     ORDER BY
           zonename
         , from_time
         , to_time
    ;
    ------------------------------------------------------------------------------
    
    ZONENAME DURATION FROM_TIME           TO_TIME            
    -------- -------- ------------------- -------------------
    Zone1    00:02:09 2013-02-22 10:24:20 2013-02-22 10:26:29
    Zone1    00:02:09 2013-02-22 11:25:15 2013-02-22 11:27:24
    Zone1    00:11:11 2013-02-28 16:13:48 2013-02-28 16:24:59
    Zone1    00:14:09 2013-02-28 16:40:18 2013-02-28 16:54:27
    
      4 record(s) selected.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought Example 1 and 2 included some redundancies.

    Here is a more compact example.

    Test Data:
    Code:
    zonename nodeid nodelabel    noderegainedservice nodelostservice    
    -------- ------ ------------ ------------------- -------------------
    Zone1        13 Windows-SRV3 2013-02-22-17.23.59 2013-02-22-10.19.13
    Zone1        13 Windows-SRV3 2013-02-28-16.54.27 2013-02-28-16.13.48
    Zone1        27 Windows-SRV1 2013-02-21-10.04.56 2013-02-21-09.48.48
    Zone1        27 Windows-SRV1 2013-02-21-10.14.01 2013-02-21-10.09.27
    Zone1        27 Windows-SRV1 2013-02-22-10.26.29 2013-02-22-10.24.20
    Zone1        27 Windows-SRV1 2013-02-22-11.27.24 2013-02-22-11.25.15
    Zone1        27 Windows-SRV1 2013-02-28-16.24.59 2013-02-28-15.52.59
    Zone1        27 Windows-SRV1 2013-02-28-16.56.19 2013-02-28-16.40.18
    Zone1        39 Windows-SRV2 2013-02-21-13.15.53 2013-02-21-12.26.04
    Zone1        39 Windows-SRV2 2013-02-23-13.23.10 2013-02-22-10.21.14
    Zone1        39 Windows-SRV2 2013-02-24-13.35.23 2013-02-23-13.33.32
    Zone1        39 Windows-SRV2 2013-02-26-15.17.25 2013-02-25-14.25.51
    Zone1        39 Windows-SRV2 2013-02-28-18.49.56 2013-02-28-15.43.01
    Zone2        13 Windows-SRV3 2013-02-22-17.23.59 2013-02-22-10.19.13
    Zone2        13 Windows-SRV3 2013-02-28-16.54.27 2013-02-28-16.13.48
    Zone2        14 Windows-SRV4 2013-02-22-11.02.56 2013-02-22-10.01.48
    
      16 record(s) selected.
    Example 3: Tested on DB2 9.7 for Windows.
    Code:
    SELECT lost.zonename
         , TRANSLATE(
              'ij:kl:mn'
            , DIGITS( MIN(regn.noderegainedservice) - lost.nodelostservice )
            , 'abcdefghijklmn'
           ) AS duration
         , CHAR(TO_CHAR(lost.nodelostservice          , 'yyyy-mm-dd hh24:mi:ss') , 19) AS from_time
         , CHAR(TO_CHAR(MIN(regn.noderegainedservice) , 'yyyy-mm-dd hh24:mi:ss') , 19) AS to_time
     FROM  outages AS lost
     INNER JOIN
           outages AS regn
      ON   regn.zonename            =  lost.zonename
       AND regn.noderegainedservice >  lost.nodelostservice
       AND regn.nodelostservice     <= lost.nodelostservice
     GROUP BY
           lost.zonename
         , lost.nodelostservice
     HAVING
           COUNT(*)
           = (SELECT COUNT(DISTINCT nodeid)
               FROM  outages z
               WHERE z.zonename = lost.zonename
             )
     ORDER BY
           zonename
         , from_time
    ;
    ------------------------------------------------------------------------------
    
    ZONENAME DURATION FROM_TIME           TO_TIME            
    -------- -------- ------------------- -------------------
    Zone1    00:02:09 2013-02-22 10:24:20 2013-02-22 10:26:29
    Zone1    00:02:09 2013-02-22 11:25:15 2013-02-22 11:27:24
    Zone1    00:11:11 2013-02-28 16:13:48 2013-02-28 16:24:59
    Zone1    00:14:09 2013-02-28 16:40:18 2013-02-28 16:54:27
    Zone2    00:43:43 2013-02-22 10:19:13 2013-02-22 11:02:56
    
      5 record(s) selected.
    Last edited by tonkuma; 05-02-13 at 03:13. Reason: Code of Example 3 was entirely replaced.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If your PostgreSQL supports Windows Functions,
    this might be another solution.

    Example 4: Tested on DB2 9.7 for Windows.
    Code:
    SELECT lost.zonename
         , TRANSLATE(
              'ij:kl:mn'
            , DIGITS( MIN(regn.noderegainedservice) - lost.nodelostservice )
            , 'abcdefghijklmn'
           ) AS duration
         , CHAR(TO_CHAR(lost.nodelostservice          , 'yyyy-mm-dd hh24:mi:ss') , 19) AS from_time
         , CHAR(TO_CHAR(MIN(regn.noderegainedservice) , 'yyyy-mm-dd hh24:mi:ss') , 19) AS to_time
     FROM  (SELECT t.*
                 , MAX( DENSE_RANK() OVER(PARTITION BY zonename
                                              ORDER BY nodeid)
                      ) OVER(PARTITION BY zonename) AS nodeid_count
             FROM  outages AS t
           ) AS lost
     INNER JOIN
           outages AS regn
      ON   regn.zonename            =  lost.zonename
       AND regn.noderegainedservice >  lost.nodelostservice
       AND regn.nodelostservice     <= lost.nodelostservice
     GROUP BY
           lost.zonename
         , lost.nodelostservice
     HAVING
           COUNT(*) = MAX(nodeid_count)
     ORDER BY
           zonename
         , from_time
    ;
    ------------------------------------------------------------------------------
    
    ZONENAME DURATION FROM_TIME           TO_TIME            
    -------- -------- ------------------- -------------------
    Zone1    00:02:09 2013-02-22 10:24:20 2013-02-22 10:26:29
    Zone1    00:02:09 2013-02-22 11:25:15 2013-02-22 11:27:24
    Zone1    00:11:11 2013-02-28 16:13:48 2013-02-28 16:24:59
    Zone1    00:14:09 2013-02-28 16:40:18 2013-02-28 16:54:27
    Zone2    00:43:43 2013-02-22 10:19:13 2013-02-22 11:02:56
    
      5 record(s) selected.
    Last edited by tonkuma; 05-04-13 at 07:51.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example using Windows Functions.

    Example 5: Tested on DB2 9.7 for Windows.
    Code:
    SELECT zonename
         , TRANSLATE(
              'ij:kl:mn'
            , DIGITS( lead_time - lost_regn_time )
            , 'abcdefghijklmn'
           ) AS duration
         , CHAR(TO_CHAR(lost_regn_time , 'yyyy-mm-dd hh24:mi:ss') , 19) AS from_time
         , CHAR(TO_CHAR(lead_time      , 'yyyy-mm-dd hh24:mi:ss') , 19) AS to_time
     FROM  (SELECT zonename
                 , lost_regn_time
                 , SUM(lost_flag)
                      OVER(PARTITION BY zonename
                               ORDER BY lost_regn_time) AS lost_count
                 , LEAD(lost_regn_time)
                      OVER(PARTITION BY zonename
                               ORDER BY lost_regn_time) AS lead_time
                 , MAX( DENSE_RANK() OVER(PARTITION BY zonename
                                              ORDER BY nodeid)
                      )
                      OVER(PARTITION BY zonename) AS nodeid_count
             FROM  outages
             CROSS JOIN LATERAL
                   (VALUES ( -1 , noderegainedservice )
                         , ( +1 , nodelostservice     )
                   ) AS f( lost_flag , lost_regn_time )
           ) r
     WHERE lost_count = nodeid_count
     ORDER BY
           zonename
         , from_time
    ;
    ------------------------------------------------------------------------------
    
    ZONENAME DURATION FROM_TIME           TO_TIME            
    -------- -------- ------------------- -------------------
    Zone1    00:02:09 2013-02-22 10:24:20 2013-02-22 10:26:29
    Zone1    00:02:09 2013-02-22 11:25:15 2013-02-22 11:27:24
    Zone1    00:11:11 2013-02-28 16:13:48 2013-02-28 16:24:59
    Zone1    00:14:09 2013-02-28 16:40:18 2013-02-28 16:54:27
    Zone2    00:43:43 2013-02-22 10:19:13 2013-02-22 11:02:56
    
      5 record(s) selected.
    Last edited by tonkuma; 05-04-13 at 08:16. Reason: Replace column name lost_regn_timestamp with lost_regn_time

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm sorry!
    LATERAL used in Example 5 may not work on PostgreSQL, now.

    I found this.
    Quote Originally Posted by shammat View Post
    This is currently not possible.

    Postgres 9.3 will be able to use LATERAL for this (which is the ANSI standard compliant implementation of SQL Server's cross apply).

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I tried to remove LATERAL from Example 5.

    Example 6: Tested on DB2 9.7 for Windows.
    Note: Italic Red were changed from Example 5.
    Code:
    SELECT zonename
         , TRANSLATE(
              'ij:kl:mn'
            , DIGITS( lead_time - lost_regn_time )
            , 'abcdefghijklmn'
           ) AS duration
         , CHAR(TO_CHAR(lost_regn_time , 'yyyy-mm-dd hh24:mi:ss') , 19) AS from_time
         , CHAR(TO_CHAR(lead_time      , 'yyyy-mm-dd hh24:mi:ss') , 19) AS to_time
     FROM  (SELECT zonename
                 , lost_regn_time
                 , SUM(lost_flag)
                      OVER(PARTITION BY zonename
                               ORDER BY lost_regn_time) AS lost_count
                 , LEAD(lost_regn_time)
                      OVER(PARTITION BY zonename
                               ORDER BY lost_regn_time) AS lead_time
                 , MAX( DENSE_RANK() OVER(PARTITION BY zonename
                                              ORDER BY nodeid)
                      )
                      OVER(PARTITION BY zonename) AS nodeid_count
             FROM  (SELECT zonename , nodeid
                         , lost_flag
                         , CASE lost_flag
                           WHEN -1 THEN noderegainedservice
                           ELSE         nodelostservice
                           END  AS lost_regn_time
                     FROM  outages
                     CROSS JOIN
                           (VALUES -1 , +1 ) AS f(lost_flag)
                   ) AS q
           ) r
     WHERE lost_count = nodeid_count
     ORDER BY
           zonename
         , from_time
    ;

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •