How to use only one query to get the data every day within one year?

Question

I want to get the data every day within one year backward but I have to use 365 queries for each day like:

for ($i = 0; $i<365; $i++){
    $end_day = ...; // the end time of each day
    $start_day = ...; // the start time of each day
    $query = select count(*)....where created < $end_day AND created > $start_day
}

I think that my current solution makes the system very slow. Is there any way to just use one query only?

Answer 1

Assuming that your created column is a DATETIME or TIMESTAMP, and the data stored is more granular than a day:

SELECT COUNT(*) ... GROUP BY YEAR(created), MONTH(created), DAY(created)

COUNT is an aggregate function and will apply to each group, i.e. you will have one row per group and that row will have the number of records in that group. As we've made a group a day, this is the data you want.

You cannot just group by DAY(created) as DAY returns the day of the month, so we also need to put month and year in to make sure days are discrete.

You probably want to have a WHERE created > $start AND created < $finish to limit it to some time frame (or a WHERE YEAR(created) == 2010).

References:

MySQL Query GROUP BY day / month / year

http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html

Answer 2

You can use a group by clause. http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html

if you have time in your date you'll have to format it to get only the date (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format)

select count(*), created from my_table group by created

Answer 3

If it's a datetime column already:

SELECT COUNT(*),DATE(created) as d ..... GROUP BY d;

If it's stored as a unix timestamp:

SELECT COUNT(*),DATE(FROM_UNIXTIME(created)) as d ..... GROUP BY d;

Basically, you can use the Mysql Date and Time functions to format the column such that you get just a date out of it, and then group by those dates to get a count for each date.