Group within a certain time period?

[sciencebear]

I have a certain table, updates, with columns id, user, type, date, and time. I am trying to group them and get a count of how many in each group by user, type, and within a certain time period. However, I can't seem to get the last part to work. I can group them by user, type and day with the following query:

 

SELECT *,COUNT(type) FROM updates GROUP BY user,type,date ORDER BY id DESC

 

However, like I said, I need to do the same thing except narrow down the time frame. The problem is that I can't just do "GROUP BY user,type,time" because that would only do updates at the exact same second. I want to group updates that are the same user and type and about 30 minutes apart. Any suggestions?

[straylight]

You could try something like this:

 

SELECT *, COUNT(type) 
FROM updates 
GROUP BY 
user,type,
GROUP BY ((60/30) * HOUR(date) + FLOOR(MINUTE(date) / 30)) 
ORDER BY id DESC

 

The "30" bit can be replaced with whatever minute interval you need.

[straylight]

Oops, just realised I made a mistake in that query. It should really look like this:

 

SELECT *, COUNT(type)
FROM updates
GROUP BY
   user,type,
   ((60/30) * HOUR(date) + FLOOR(MINUTE(date) / 30))
ORDER BY id DESC

[sciencebear]

Thanks! I had to change it a little because of the way my table was set up (date and time in separate columns) but the below code works great. Thank you very much!

 

SELECT *, COUNT(type)
FROM updates
GROUP BY
   user,type,date,
   ((60/30) * HOUR(time) + FLOOR(MINUTE(time) / 30))
ORDER BY id DESC