How can I group by every 15 minutes?

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

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.

Code:

---

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

---

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.

Code:

---

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:

Code:

---

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

---

Quote:

---

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.

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:

Code:

---

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