Q
Problem solve Get help with specific problems with your technologies, process and projects.

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;
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

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;

This was last published in September 2003

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close