Counting number of transactions in a variable time

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;

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)

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;