How to group timestamps in a 10 minute bucket and aggregate

Question

I have a table with a column "hit_timestamp" with time stamp datatype. below are some of the values.

20-MAR-17 00.00.00.000000000
20-MAR-17 00.03.53.000000000
20-MAR-17 00.01.16.000000000
20-MAR-17 00.07.32.000000000
20-MAR-17 00.18.36.000000000
20-MAR-17 00.16.12.000000000

I want to group then in 10 minutes buckets

time_bucket        no_of_records
----------          ---------
"000000_000010"     4
"000011_000020"     2

Before jumping to writing code of any kind, you need to spend some time thinking about the difference between "discrete" values (such as counting people in a room) and "continuous" ones (such as time). Specifically in your time_bucket descriptions, where will you fit an event that happens at 00.10.23? It is neither between 0 and 10 nor between 11 and 20. You may write the greatest code in the world, if you get this wrong your results will be wrong anyway. — , Aug 21 '17 at 18:05
Please Edit your post again and pay attention to tags. Are you really using all of MySQL, SQL Server, and Oracle? — , Aug 21 '17 at 18:06
Possible duplicate of [SQL SERVER - Group records by n minutes interval](https://stackoverflow.com/questions/26788729/sql-server-group-records-by-n-minutes-interval) — Tab Alleman, Aug 21 '17 at 19:27

score 5 · Accepted Answer · answered Aug 21 '17 at 18:26

For 10 minute intervals, the easiest way to proceed is to get the string format of your date, and remove anything at the right of the first digit of the 2-digit minute part. So 20-03-17 00.18.36 would be stripped to 20-03-17 00.1. That would work well as the value to group by. For displaying purposes you could then add 0.00 to it for denoting the lower bound of the bucket, and 9.59 for the higher bound.

Now the function to convert a timestamp to a string is different in different database engines.

For Oracle it would look like this:

select   substr(to_char(dat, 'YY-MM-DD HH24.MI'), 1, 13) || '0.00' bucket_start,
         substr(to_char(dat, 'YY-MM-DD HH24.MI'), 1, 13) || '9.59' bucket_end,
         count(*)
from     mytable
group by substr(to_char(dat, 'YY-MM-DD HH24.MI'), 1, 13)
order by 1

Output:

   BUCKET_START   |    BUCKET_END     | COUNT(*)
------------------+-------------------+---------
17-03-20 00.00.00 | 17-03-20 00.09.59 |    4
17-03-20 00.10.00 | 17-03-20 00.19.59 |    2

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

You can round down hti_teimstamp to an interval of every 10 minutes, then do a group by.

"2017-03-20 00:03:53.0000000" becomes "2017-03-20 00:00:00.000"

"2017-03-20 00:18:36.0000000" becomes "2017-03-20 00:10:00.000"

select dateadd(minute, datediff(minute, 0, hit_timestamp)/10*10 ,0) as time_bucket, count(1) as no_of_records
from #temp
group by dateadd(minute, datediff(minute, 0, hit_timestamp)/10*10 ,0)

How to group timestamps in a 10 minute bucket and aggregate

2 Answers2