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

## SearchDataManagement

• ### Why consider an augmented data catalog?

Automated and augmented data catalogs have been around for a few years, but adoption is still lagging. Find out why an enterprise...

• ### Alation brings data catalog technology to the public cloud

Alation's data intelligence technology is getting easier for organizations to use, with a new managed service that can help ...

• ### Yellowbrick Manager embraces Kubernetes for data warehouse

Yellowbrick is building out a new unified control plane to help users manage distributed cloud data warehouse deployments. The ...

• ### Air Force's data overhaul makes analytics a priority

With its data locked in legacy systems that didn't communicate with one another, in 2017 the Air Force started to overhaul of its...

• ### 6 reasons you may need data science as a service

There are plenty of reasons to outsource all or part of a data science project to a service. Find out how enterprises are using ...

• ### Logi Analytics acquired by ERP vendor Insightsoftware

Logi Analytics, a BI vendor specializing in embedded business intelligence, was acquired on April 7 by an ERP vendor specializing...

## SearchSAP

• ### Unpatched applications threaten SAP security

Cyberattacks are a significant threat to unpatched, unprotected SAP applications, according to a new threat intelligence report ...

• ### Understand your S/4HANA deployment options

SAP deployment options can be confusing, particularly with name changes. Learn which SAP program is right for your company based ...

• ### MSC Industrial Supply transforms to SAP S/4HANA private cloud

MSC Industrial Supply needed to cut costs and modernize its ERP system, so it first moved to S/4HANA on a hybrid cloud and has ...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### Microsoft previews OpenJDK distro to the delight of devs

In a move meant to attract more Java developers to its Azure cloud and further support the Java community, Microsoft launched a ...

• ### Supreme Court ruling on Java APIs eases developer worries

Now that the Supreme Court has ruled for Google over Oracle in their high-stakes copyright battle over Java APIs, developers can ...

• ### Genuitec's CodeTogether 4.0 promotes pair programming

With Genuitec CodeTogether 4.0, development teams can work collaboratively even when remote. The product supports pair ...

## SearchDataCenter

• ### New Intel Ice Lake processors boost performance, security

Intel launches third-generation Xeon Scalable processors that bolster security, accelerate common data center workloads by 46% on...

• ### IBM tools speed mainframe application modernization projects

IBM has released new versions of its application modernization tools designed to bring its Z series of mainframe applications in ...

• ### Nvidia vs. AMD: Compare GPU offerings

Chip vendors Nvidia and AMD each offer GPUs optimized for large data centers. Compare the two to decide which best suits your ...

## SearchContentManagement

As the pandemic disrupts paper workflows, Adobe courts small business users with simple webforms, digital signatures and payments...

• ### Know when to use a headless CMS and when it's not worth it

Headless CMSes aren't a silver bullet for every circumstance. Evaluate three solid use cases for a headless CMS and three ...

• ### 8 business benefits and challenges of video conferencing

Remote work necessitates software such as video conferencing software. Consider both the business benefits and the challenges ...

## SearchHRSoftware

• ### Hybrid workplace may give employers hiring edge

The post-pandemic office is leaning strongly in the direction of hybrid workplaces. Job candidates view working from home as a ...

• ### Biden infrastructure plan may deliver huge training boost

President Biden's \$2T infrastructure plan will likely result in training programs for rapid reskilling, especially in the ...

• ### 10 learning and development trends for the next normal

Today's companies need to upskill and reskill their workforces to stay competitive. Here's a look some of the most important ...

Close