| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
10-13-11, 13:31
|
|
Registered User
|
|
Join Date: Jun 2011
Posts: 3
|
|
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')
|
|
10-13-11, 14:47
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
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
|
|
10-13-11, 14:49
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
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
;
|
|
10-13-11, 14:53
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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.
|
|
10-13-11, 16:42
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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 */
;
|
|
10-14-11, 09:21
|
|
Registered User
|
|
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
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 09:26.
|
10-14-11, 11:17
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
|
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.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Grouping by time intervals
