[Chartjs]-PHP Date and Time, getting the 1st, 2nd, 3rd, and 4th week in a month

You can get a nice tidy count in a single query like this:

SELECT
    CEIL(DAYOFMONTH(FROM_UNIXTIME(date_created)) / 7) AS week_of_month,
    COUNT(id) AS tickets_per_week
FROM tickets
WHERE YEAR(FROM_UNIXTIME(date_created)) = ?
AND MONTH(FROM_UNIXTIME(date_created)) = ?
GROUP BY `week_of_month`
ORDER BY `week_of_month` ASC

Note if you used native datetime or timestamp fileds, you could get rid of all of those FROM_UNIXTIME conversions. This is based on a “week” being the first 7 days of the month, not based on specific days of the week. If you wanted it based on fixed weeks (in terms of sunday through saturday or such) you could just use WEEK() function in the SELECT instead.

That might look like this:

SELECT
    WEEK(FROM_UNIXTIME(date_created), 0) AS week_number,
    COUNT(id) AS tickets_per_week
FROM tickets
WHERE YEAR(FROM_UNIXTIME(date_created)) = ?
AND MONTH(FROM_UNIXTIME(date_created)) = ?
GROUP BY `week_number`
ORDER BY `week_number` ASC

Here week_number would be a value between 0-53 and would not necessarily have any meaning for display other than as a means for aggregation. I am using mode 0 for WEEK() functoin as this specifies Sun-Sat week. You can look at the definitions here and determine what mode suits you the best: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week

None of these queries are optimized because you won’t be able to use an index on date_created . If you do get around to changing that column type to a datetime or timestamp, you would also perhaps want to change you WHERE condition to the slightly less easy to read, but more index friendly version like this:

WHERE date_created BETWEEN '2014-12-01 00:00:00' AND '2014-12-31 23:59:29'