How to use only 1 query to get the data everyday within one year

Question

Hi all,

I want to get the data everyday 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 anyway to just use 1 query only?

Thank you. James

Answer 1

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 2

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.

Answer 3

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:

http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year

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