MySQL rows per hour from timestamp field

[DaveC426913]

I have records with a timestamp field in a format per this sample: 2006-04-18 21:04:30. Ultimately, I want to graph simply the number of records created per hour.

I want it on a timeline that naturally includes all years, months, days and hours from the earliest record to the latest. i.e. my timeline would look like this:

2006-04-18 21:00 : 5
2006-04-18 22:00 : 4
2006-04-18 23:00 : 3
2006-04-19 00:00 : 0
2006-04-18 01:00 : 2
...
2007-01-01 00:00 : 4
etc.

As you can see, it should not skip rows that have zero records.

I started like this:

$sql = "SELECT MIN(StartTime),MAX(StartTime) FROM `AuraUsers` WHERE StartTime IS NOT NULL";
if ($debug==1){ echo $sql."<br>\n"; }
$result = mysql_query($sql);
for ($y=mysql_fetch_array($result,0);$y<=mysql_fetch_array($result,1);$y++){
$sql = "SELECT StartTime FROM `AuraUsers` WHERE StartTime LIKE '".$y."%'";
if ($debug==1){ echo $sql."<br>\n"; }
$result = mysql_query($sql);
}

but I'm thinking this is going to be VERY clunky, since I'll bascially be nesting a loop for each time unit (year, then month, then day, then hour). Also, It'll get real complex trying to loop 28/30/31 days in a month, etc.

Can anyone suggest - or point me at - something more elegant?

 

[sleipnir214]

I have a partial solution that is more elegant.

I say "partial" because the query I'm thinking of would not return values for hours with no records. You'll have to catch that in your PHP code. But given the table "foo":

[tt]+-----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| the_stamp | datetime | NO | | | |
| some_data | int(11) | YES | | NULL | |
+-----------+----------+------+-----+---------+----------------+
[/tt]

containing the data:

[tt]+----+---------------------+-----------+
| Id | the_stamp | some_data |
+----+---------------------+-----------+
| 1 | 2006-04-19 10:15:00 | 25 |
| 2 | 2006-04-19 10:30:00 | 30 |
| 3 | 2006-04-19 10:45:00 | 35 |
| 4 | 2006-04-19 11:00:00 | 10 |
| 5 | 2006-04-19 11:15:00 | 15 |
| 6 | 2006-04-19 11:30:00 | 30 |
| 7 | 2006-04-19 12:15:00 | 15 |
| 8 | 2006-04-19 12:30:00 | 30 |
| 9 | 2006-04-19 12:45:00 | 45 |
| 10 | 2006-04-19 14:15:00 | 1 |
+----+---------------------+-----------+
[/tt]

the query:

SELECT
date_format(the_stamp, '%Y-%m-%d %H:00') as the_hour,
COUNT(*)
FROM
foo
GROUP BY
the_hour

will return:

[tt]+------------------+----------+
| the_hour | COUNT(*) |
+------------------+----------+
| 2006-04-19 10:00 | 3 |
| 2006-04-19 11:00 | 3 |
| 2006-04-19 12:00 | 3 |
| 2006-04-19 14:00 | 1 |
+------------------+----------+
[/tt]

your script will have to detect missing hours, but at least MySQL is doing more of the work....


Want the best answers? Ask the best questions! TANSTAAFL!