I have a table of transactions including a TIMESTAMP column (Oracle) that contains the date and time (to seconds) of each transaction. The following SQL allows me to count the number of transactions in a minute:
SELECT TO_CHAR (msgdate, 'MMDDYYYY HH24MI'), COUNT (*) FROM txnstats GROUP BY TO_CHAR (msgdate, 'MMDDYYYY HH24MI') ORDER BY 1;But, I need to count the number of transactions for a variable time slice - e.g. 5 minutes.
Using TO_CHAR (dt, 'SSSSS') it's not hard to write a function that rounds a time down to an integer multiple of x minutes past midnight. For example
round_time (msgdate, 7)would round the time component down to an integer multiple of 7 minutes past midnight (00:00, 00:07, 00:14, ..., 23:55). In other words, this function would return a value representing 00:00 if given any input from 00:00 up to (but not including) 00:07. If you pick a weird value (like 7 minutes) for the time slice you may wind up with a small bucket at the end of each day; but any scheme which avoids that problem would introduce another problem: having buckets for different times on different days (00:00 to 00:07 on Sunday, 00:02 to 00:09 on Monday, etc.) Stick to numbers like 3, 4, 5, 6, 10, 12 or 15 and you won't have either problem.
In the example below the first argument is a DATE. If you pass a TIMESTAMP, Oracle will automatically convert it.
Another thing (not that you asked): when I GROUP BY a function, I like to use an in-line view because
- it guarantees the function won't be evaluated twice
- it guarantees that what you SELECT is the same as what you GROUP BY
- it's easier to maintain: if you need to change it, you only have to change one thing
- you can ORDER BY name, if you want to
SELECT TO_CHAR (approx_msgdate, 'MMDDYYYY HH24:MI') AS msgdate COUNT (*) FROM ( SELECT round_time (msgdate, 5) AS approx_msgdate FROM txnstats ) GROUP BY approx_msgdate ORDER BY approx_msgdate;
FUNCTION round_time ( in_dt IN DATE, -- date/time to be rounded in_minute_val IN NUMBER, -- slice size, in minutes in_second_val IN NUMBER DEFAULT 0 -- additional slice size, in seconds ) RETURN DATE IS dt_text CHAR (14); -- 'YYYYMMDD SSSSS' format second_val PLS_INTEGER; -- seconds past midnight BEGIN dt_text := TO_CHAR (in_dt, 'YYYYMMDD SSSSS'); second_val := TO_NUMBER ( SUBSTR ( dt_text, 10, 5 ), '99990' ); -- round it down second_val := second_val - MOD ( second_val, (60 * in_minute_val) + in_second_val ); -- re-build dt_text with new time dt_text := SUBSTR (dt_text, 1, 9) || TRIM (TO_CHAR (second_val, '00000')); RETURN TO_DATE (dt_text, 'YYYYMMDD SSSSS'); END;