Take the 2-minute tour ×

I have an issue that I can't solve by myself. The best thing would be if it's possible to do it directly from mysql but if not, calculating from php is also good.

I have a table that has 3 columns: id, time, value

Now the time value is datetime (example: 2012-11-03 00:13:12)

I want to create statistics from the last 7 days. Let's say I have 7 rows and today is 2012-12-20.

Here are the rows: datetime value
2012-12-20 00:12:12 2
2012-12-20 04:06:15 4
2012-12-20 03:12:14 5
2012-12-18 03:12:14 7
2012-12-16 12:12:14 3
2012-12-16 14:23:34 2
2012-12-15 14:23:34 9

If I want to get the last 7 days report, it would be this:

2012-12-14 -> value =0
2012-12-15 -> value =9

2012-12-16 -> value =5

2012-12-17 -> value =0

2012-12-18 -> value =7
2012-12-19 -> value =0

2012-12-20 -> value =11

Currently I have this (extract last 7 day records):

$date=date ("Y-m-d", strtotime ( '-7 day' . $date ) );  
$qry="select value,date from mytable where date>'$date'";
$res=mysql_query($qry) or die("Couldn't select Information : ".mysql_error());
        echo $row["value"];

I hope somebody can help with advice. Thanks in advance! Vlad

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

This should be relatively easy to solve using just MySQL. This is very similar to this question: MySQL Query GROUP BY day / month / year

You need to change your query so it gets all the records for a given week.

SELECT DAY(date) , SUM(value)
FROM mytable

Its also very easy to change how you group the numbers, for example by month using GROUP BY MONTH(date). You could also make the MySQL display the day name as a field by doing SELECT DAYNAME(date) For more details on what functions are available check out the MySQL User Guide on Date and Time functions.

share|improve this answer
Thank you, exactly what I was looking for! I am using DATE_FORMAT(date,'format') because I want to also get the year/month, but it's perfect! –  BlasterGod Dec 21 '11 at 21:29
add comment

Your Answer


