Grouping timestamps in MySQL with PHP

Question

I want to log certain activities in MySql with a timecode using time(). Now I'm accumulating thousands of records, I want to output the data by sets of hours/days/months etc.

What would be the suggested method for grouping time codes in MySQL?

Example data:

• 1248651289
• 1248651299
• 1248651386
• 1248651588
• 1248651647
• 1248651700
• 1248651707
• 1248651737
• 1248651808
• 1248652269

Example code:

$sql = "SELECT COUNT(timecode) FROM timecodeTable";
//GROUP BY round(timecode/3600, 1) //group by hour??

Edit: There's two groupings that can be made so I should make that clearer: The 24 hours in the day can be grouped but I'm more interested in grouping over time so returning 365 results for each year the tracking is in place, so total's for each day passed, then being able to select a range of dates and see more details on hours/minutes accessed over those times selected.

This is why I've titled it as using PHP, as I'd expect this might be easier with a PHP loop to generate the hours/days etc?

Answer 1

Peter

SELECT COUNT(*), HOUR(timecode)
FROM timecodeTable
GROUP BY HOUR(timecode);

Your result set, given the above data, would look as such:

+----------+----------------+
| COUNT(*) | HOUR(timecode) |
+----------+----------------+
|       10 |             18 | 
+----------+----------------+

Many more related functions can be found here.

Edit

After doing some tests of my own based on the output of your comment I determined that your database is in a state of epic fail. :) You're using INT's as TIMESTAMPs. This is never a good idea. There's no justifiable reason to use an INT in place of TIMESTAMP/DATETIME.

That said, you'd have to modify my above example as follows:

SELECT COUNT(*), HOUR(FROM_UNIXTIME(timecode))
FROM timecodeTable
GROUP BY HOUR(FROM_UNIXTIME(timecode));

Edit 2

You can use additional GROUP BY clauses to achieve this:

SELECT 
  COUNT(*),
  YEAR(timecode),
  DAYOFYEAR(timecode),
  HOUR(timecode)
FROM timecodeTable
GROUP BY YEAR(timecode), DAYOFYEAR(timecode), HOUR(timecode);

Note, I omitted the FROM_UNIXTIME() for brevity.