How can I group by every 15 minutes?

[HoraShadow]

Hi guys,

I am doing statistics for a web site, and I found myself stuck in a query. The engine is MySQL 5.

I know how to solve it from code, that's not the issue, but I found it puzzling hard to do it entirely in SQL language and I want to learn the answer.

Basically, I have a table with date entries that I have to group the entries every 15 minutes, and have a count of how many entries were in those 15 minutes.

Example of the table:

2007-09-25 10:01
2007-09-25 10:03
2007-09-25 10:05
2007-09-25 10:09
2007-09-25 10:12
2007-09-25 10:14
2007-09-25 10:16
2007-09-25 10:19
2007-09-25 10:21
2007-09-25 10:29

The goal is to return two rows, the first row saying 6 and the second row saying 4.

Ideas on how can this be done by SQL language?

Thanks,
HoraShadow

[brucevde]

If all records have the same date, then convert the time to milliseconds and divide by 900000 (# milliseconds in 15 minutes). This works in SQL Server.

Create Table #tmp (EntryDate datetime)

Insert Into #tmp Values('2007-09-25 10:01')
Insert Into #tmp Values('2007-09-25 10:03')
Insert Into #tmp Values('2007-09-25 10:05')
Insert Into #tmp Values('2007-09-25 10:09')
Insert Into #tmp Values('2007-09-25 10:12')
Insert Into #tmp Values('2007-09-25 10:14')
Insert Into #tmp Values('2007-09-25 10:16')
Insert Into #tmp Values('2007-09-25 10:19')
Insert Into #tmp Values('2007-09-25 10:21')
Insert Into #tmp Values('2007-09-25 10:29')


Select Count(*) From #tmp
Group By DateDiff(ms, Cast(Convert(varchar(10), EntryDate,112) as Datetime), EntryDate) / 900000

Drop Table #Tmp

[kaffenils]

There is a "little" bug in your code brucevde: It only groups by time, not date.

Add this row to your sample and execute. You'll se that the row will be grouped together with rows on different dates.

Insert Into #tmp Values('2007-09-26 10:29')

One solution is to group by year,month,date, hour and 15-minutes-parts like this:

select count(*)
from #tmp
group by 
year(EntryDate),
month(EntryDate),
day(EntryDate),
datepart(hh,EntryDate),
datepart(mi,EntryDate)/15

[brucevde]

There is a "little" bug in your code brucevde:

I got around it by saying "If all records have the same date,"...

But that is no excuse for the crappy code I wrote. No idea what I was thinking when I wrote it.

[HoraShadow]

Very nice guys...

Both versions worked just fine in SQL Server, but I could not migrate brucevde's version to MySQL.

This is the MySQL version of kaffenils' query:

SELECT count(*) as Count, DATE_FORMAT(EntryDate, "%Y-%m-%d %H") as DateTime, FLOOR(DATE_FORMAT(EntryDate, "%i")/15) as Quadrant
FROM tmp
GROUP BY
YEAR(EntryDate),
MONTH(EntryDate),
DAY(EntryDate),
HOUR(EntryDate),
DATE_FORMAT(EntryDate, "%I"),
FLOOR(DATE_FORMAT(EntryDate, "%i")/15)

Thanks, I really wanted to know how to do it strictly in SQL language...
HoraShadow