Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    3

    Question Unanswered: Grouping by time intervals

    I am attempting to group records by 12 minute intervals. I.E. 12:00pm-12:12pm, 12:13pm-12:25pm, etc. I am currently grouping in 10 minute intervals...although the current method won't exactly work for 12. Can anyone give some advice on this? I've seen similar questions but nothing for DB2 or quite like this. See the SQL below:

    Code:
    SELECT TIMESTAMP(LEFT(char(nvs.TXN_DT_TM),13) || '.' || RIGHT(LEFT(char(nvs.TXN_DT_TM),15),1) || '0.00.000000') as YYYY_MM_DD_HH_10MM,
        count(DISTINCT nvs.UZRL) AS "Unique Users",
        cast(count(DISTINCT nvs.UZRL) as float)/6 AS "Labor Hours"
    FROM db.table nvs
    WHERE nvs.TXN_DT_TM >= '2011-01-01-00.00.00' and date(nvs.TXN_DT_TM) >= (current date)
    AND (nvs.MVKD = 'RDSC' AND nvs.PFHS = 'Y')
    GROUP BY TIMESTAMP(LEFT(char(nvs.TXN_DT_TM),13) || '.' || RIGHT(LEFT(char(nvs.TXN_DT_TM),15),1) || '0.00.000000')

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I have not tried it, but why not use:

    Code:
    group by date(nvs.TXN_DT_TM),hour(nvs.TXN_DT_TM),minute(nvs.TXN_DT_TM)/12
    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?
    (I like to use capital for keywords/built-in functions and lower case for other names.)
    Code:
    SELECT   TIMESTAMP( DATE(nvs.txn_dt_tm) )
           + HOUR(nvs.txn_dt_tm) HOURs
           + ( MINUTE(nvs.txn_dt_tm) / 12 * 12 ) MINUTEs       AS yyyy_mm_dd_hh_12mi
         , COUNT(DISTINCT nvs.uzrl)                            AS "Unique Users"
         , CAST( COUNT(DISTINCT nvs.uzrl) AS DEC(31 , 2) ) / 5 AS "Labor Hours"
     FROM  db.table nvs
     WHERE nvs.txn_dt_tm >= '2011-01-01-00.00.00'
       AND nvs.txn_dt_tm >= current_date
       AND nvs.mvkd = 'RDSC'
       AND nvs.pfhs = 'Y'
     GROUP BY
           DATE  (nvs.txn_dt_tm)
         , HOUR  (nvs.txn_dt_tm)
         , MINUTE(nvs.txn_dt_tm) / 12
    ;

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is a simplified test result.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT   TIMESTAMP( DATE(nvs.txn_dt_tm) )
           + HOUR(nvs.txn_dt_tm) HOURs
           + ( MINUTE(nvs.txn_dt_tm) / 12 * 12 ) MINUTEs  AS yyyy_mm_dd_hh_12mi
         , COUNT(/*DISTINCT nvs.uzrl*/ *) AS "Unique Users"
         , CAST( COUNT(/*DISTINCT nvs.uzrl*/ *) AS DEC(31 , 2) ) / 5 AS "Labor Hours"
         , MIN(txn_dt_tm) AS min_txn_dt_tm
         , MAX(txn_dt_tm) AS max_txn_dt_tm
    /*
     FROM  db.table nvs
    */
     FROM  (VALUES current_timestamp
                 , current_timestamp +  1 MINUTE
                 , current_timestamp +  2 MINUTE
                 , current_timestamp +  3 MINUTE
                 , current_timestamp +  4 MINUTE
                 , current_timestamp +  5 MINUTE
                 , current_timestamp +  6 MINUTE
                 , current_timestamp +  7 MINUTE
                 , current_timestamp +  8 MINUTE
                 , current_timestamp +  9 MINUTE
                 , current_timestamp + 10 MINUTE
                 , current_timestamp + 11 MINUTE
                 , current_timestamp + 12 MINUTE
                 , current_timestamp + 13 MINUTE
                 , current_timestamp + 24 MINUTE
                 , current_timestamp + 48 MINUTE
                 , current_timestamp - MIDNIGHT_SECONDS(current_timestamp) SECONDS - MICROSECOND(current_timestamp) MICROSECOND
                 , current_timestamp - ( MIDNIGHT_SECONDS(current_timestamp) + 1 ) SECONDS  /***** out of range *****/
                 , current_timestamp - MIDNIGHT_SECONDS(current_timestamp) SECONDS + 1 DAY
           ) nvs(txn_dt_tm)
     WHERE nvs.txn_dt_tm >= '2011-01-01-00.00.00'
       AND nvs.txn_dt_tm >= current_date
    /*
       AND nvs.mvkd = 'RDSC'
       AND nvs.pfhs = 'Y'
    */
     GROUP BY
           DATE  (nvs.txn_dt_tm)
         , HOUR  (nvs.txn_dt_tm)
         , MINUTE(nvs.txn_dt_tm) / 12
    ;
    ------------------------------------------------------------------------------
    
    YYYY_MM_DD_HH_12MI  Unique Users Labor Hours                       MIN_TXN_DT_TM              MAX_TXN_DT_TM             
    ------------------- ------------ --------------------------------- -------------------------- --------------------------
    2011-10-14-00.00.00            1                              0.20 2011-10-14-00.00.00.000000 2011-10-14-00.00.00.000000
    2011-10-14-03.36.00            8                              1.60 2011-10-14-03.40.53.196000 2011-10-14-03.47.53.196000
    2011-10-14-03.48.00            6                              1.20 2011-10-14-03.48.53.196000 2011-10-14-03.53.53.196000
    2011-10-14-04.00.00            1                              0.20 2011-10-14-04.04.53.196000 2011-10-14-04.04.53.196000
    2011-10-14-04.24.00            1                              0.20 2011-10-14-04.28.53.196000 2011-10-14-04.28.53.196000
    2011-10-15-00.00.00            1                              0.20 2011-10-15-00.00.00.196000 2011-10-15-00.00.00.196000
    
      6 record(s) selected.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another way may be to replace "HOUR(nvs.txn_dt_tm)" and "MINUTE(nvs.txn_dt_tm) / 12"
    with "MIDNIGHT_SECONDS(nvs.txn_dt_tm) / 720", like...
    Code:
    SELECT TIMESTAMP( DATE(nvs.txn_dt_tm) )
         + ( MIDNIGHT_SECONDS(nvs.txn_dt_tm) / 720 * 720 ) SECONDs  AS yyyy_mm_dd_hh_12mi
    ...
    ...
     GROUP BY
           DATE(nvs.txn_dt_tm)
         , MIDNIGHT_SECONDS(nvs.txn_dt_tm) / 720 /* 720seconds = 12min * 60min/sec */
    ;

  6. #6
    Join Date
    Jun 2011
    Posts
    3
    After an initial test, this seems to work perfectly.

    Thanks!

    EDIT: Although perhaps I am using an older version of DB2 because I had to edit the first line with the following: TIMESTAMP( DATE(nvs.txn_dt_tm), '00:00:00' )

    Quote Originally Posted by tonkuma View Post
    Here is a simplified test result.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT   TIMESTAMP( DATE(nvs.txn_dt_tm) )
           + HOUR(nvs.txn_dt_tm) HOURs
           + ( MINUTE(nvs.txn_dt_tm) / 12 * 12 ) MINUTEs  AS yyyy_mm_dd_hh_12mi
         , COUNT(/*DISTINCT nvs.uzrl*/ *) AS "Unique Users"
         , CAST( COUNT(/*DISTINCT nvs.uzrl*/ *) AS DEC(31 , 2) ) / 5 AS "Labor Hours"
         , MIN(txn_dt_tm) AS min_txn_dt_tm
         , MAX(txn_dt_tm) AS max_txn_dt_tm
    /*
     FROM  db.table nvs
    */
     FROM  (VALUES current_timestamp
                 , current_timestamp +  1 MINUTE
                 , current_timestamp +  2 MINUTE
                 , current_timestamp +  3 MINUTE
                 , current_timestamp +  4 MINUTE
                 , current_timestamp +  5 MINUTE
                 , current_timestamp +  6 MINUTE
                 , current_timestamp +  7 MINUTE
                 , current_timestamp +  8 MINUTE
                 , current_timestamp +  9 MINUTE
                 , current_timestamp + 10 MINUTE
                 , current_timestamp + 11 MINUTE
                 , current_timestamp + 12 MINUTE
                 , current_timestamp + 13 MINUTE
                 , current_timestamp + 24 MINUTE
                 , current_timestamp + 48 MINUTE
                 , current_timestamp - MIDNIGHT_SECONDS(current_timestamp) SECONDS - MICROSECOND(current_timestamp) MICROSECOND
                 , current_timestamp - ( MIDNIGHT_SECONDS(current_timestamp) + 1 ) SECONDS  /***** out of range *****/
                 , current_timestamp - MIDNIGHT_SECONDS(current_timestamp) SECONDS + 1 DAY
           ) nvs(txn_dt_tm)
     WHERE nvs.txn_dt_tm >= '2011-01-01-00.00.00'
       AND nvs.txn_dt_tm >= current_date
    /*
       AND nvs.mvkd = 'RDSC'
       AND nvs.pfhs = 'Y'
    */
     GROUP BY
           DATE  (nvs.txn_dt_tm)
         , HOUR  (nvs.txn_dt_tm)
         , MINUTE(nvs.txn_dt_tm) / 12
    ;
    ------------------------------------------------------------------------------
    
    YYYY_MM_DD_HH_12MI  Unique Users Labor Hours                       MIN_TXN_DT_TM              MAX_TXN_DT_TM             
    ------------------- ------------ --------------------------------- -------------------------- --------------------------
    2011-10-14-00.00.00            1                              0.20 2011-10-14-00.00.00.000000 2011-10-14-00.00.00.000000
    2011-10-14-03.36.00            8                              1.60 2011-10-14-03.40.53.196000 2011-10-14-03.47.53.196000
    2011-10-14-03.48.00            6                              1.20 2011-10-14-03.48.53.196000 2011-10-14-03.53.53.196000
    2011-10-14-04.00.00            1                              0.20 2011-10-14-04.04.53.196000 2011-10-14-04.04.53.196000
    2011-10-14-04.24.00            1                              0.20 2011-10-14-04.28.53.196000 2011-10-14-04.28.53.196000
    2011-10-15-00.00.00            1                              0.20 2011-10-15-00.00.00.196000 2011-10-15-00.00.00.196000
    
      6 record(s) selected.
    Last edited by kaledev; 10-14-11 at 10:26.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    EDIT: Although perhaps I am using an older version of DB2 because I had to edit the first line with the following: TIMESTAMP( DATE(nvs.txn_dt_tm), '00:00:00' )
    I think so. TIMESTAMP with only one argument of DATE datatype is supported from DB2 9.7 for LUW.
    On older versions, if only one argument is specified for a TIMESTAMP function,
    the argument must be a timestamp, a valid string representation of a timestamp or a string of length 14 that is a string of digits that represents a valid date and time in the form yyyyxxddhhmmss.

Posting Permissions

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