Counting how many times something occurs every ten minutes

[Kiselhuis]

Hi All,

 

New guy here. Been playing around with php and mysql for the last few months now and am working on a little project for my office.

 

I have a program that generates a history log of when someone goes to the canteen and takes lunch. In the history file I keep name, time, date, serving station. The time and date are date() variable and I believe them to be formatted correctly.

 

My objective is to be able figure out when the busiest periods are for the canteen and which serving stations are the busiest, so getting a count of lunches served every 10 minutes per station is my goal. That way we can add additional staff to the busy stations or schedule lunch for people differently as to make the running of the canteen smoother.

 

Thanks,

K

[jskywalker]

mysql> desc testdate;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | tinyint(4) | NO   | PRI | NULL    | auto_increment |
| dt    | datetime   | NO   |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select id, dt, (0+dt)%600 period from testdate;
+----+---------------------+------------+
| id | dt                  | period     |
+----+---------------------+------------+
|  1 | 2008-11-14 12:14:08 | 408.000000 |
|  2 | 2008-11-14 12:14:22 | 422.000000 |
|  3 | 2010-02-04 12:19:27 | 127.000000 |
+----+---------------------+------------+
3 rows in set (0.00 sec)

 

In this example the datetime is converted to an integer (0+dt)

using the mod function '%' its devided into periods of 10 minutes (600 seconds)

 

you can use 'GROUP BY' on this field, to get the results you want..?

[Kiselhuis]

This looks like a great idea, thanks jskywalker! I'm gonna give this a try and see how far I get.