Harry Harry - 1 year ago 140
MySQL Question

Trying to get a group count per 15 minutes

In a old database a date column is stored as 12-10-2012 15:20:36. I need to know the usage in a timeframe of 15 minutes. I grabbed parts of information from here but this script below gives me zero results. What do I miss?

$stats = mysql_query("SELECT
count(id) as count,
WHEN minute(STR_TO_DATE(datum, '%d-%m-%Y %h:%i:%s')) BETWEEN 0 and 14 THEN '00'
WHEN minute(STR_TO_DATE(datum, '%d-%m-%Y %h:%i:%s')) BETWEEN 15 and 29 THEN '15'
WHEN minute(STR_TO_DATE(datum, '%d-%m-%Y %h:%i:%s')) BETWEEN 30 and 44 THEN '30'
WHEN minute(STR_TO_DATE(datum, '%d-%m-%Y %h:%i:%s')) BETWEEN 45 and 59 THEN '45'
END AS intervals
FROM errors
GROUP BY intervals");
while($stat = mysql_fetch_object($stats)) {
echo $stat->intervals." - ".$stat->count."<br>";

Answer Source

Try a %d-%m-%Y %H:%i:%s instead of %d-%m-%Y %h:%i:%s. %H will accept 0-23 as value for hour.

SELECT STR_TO_DATE( '12-10-2012 15:20:36', '%d-%m-%Y %H:%i:%s' ) will return 12-10-2012 15:20:36

SELECT STR_TO_DATE( '12-10-2012 15:20:36', '%d-%m-%Y %h:%i:%s' ) will return NULL

Sometimes size matters :)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download