MySQL Record Count by Date

A message table can provide daily or hourly counts without maintaining a separate counter. Define the reporting interval, restrict the rows, then group them into the required time buckets. These examples retain the table name ChatMaster and column start_date; adapt them to your schema.

Count records by day

The column should be DATETIME or TIMESTAMP. Storing the date and time together is not a defect. September 2018 is simply an illustrative reporting interval.

SELECT DATE(start_date) AS day, COUNT(*) AS total
FROM ChatMaster
WHERE start_date >= '2018-09-01 00:00:00'
  AND start_date <  '2018-10-01 00:00:00'
GROUP BY DATE(start_date)
ORDER BY day;

The lower bound is inclusive and the upper bound exclusive. This includes fractional timestamps such as 23:59:59.123456 on the final day. DATE() supplies the grouping key while the filter uses a range on the original column. See the MySQL date and time functions.

Count one day's records by hour

SELECT HOUR(start_date) AS hour, COUNT(*) AS total
FROM ChatMaster
WHERE start_date >= '2018-09-11 00:00:00'
  AND start_date <  '2018-09-12 00:00:00'
GROUP BY HOUR(start_date)
ORDER BY hour;

This interval covers one day. If you select several days but group only by HOUR(), matching hours from all days are combined. For a separate hourly series on each day, group by both components:

SELECT DATE(start_date) AS day,
       HOUR(start_date) AS hour,
       COUNT(*) AS total
FROM ChatMaster
WHERE start_date >= '2018-09-01 00:00:00'
  AND start_date <  '2018-10-01 00:00:00'
GROUP BY DATE(start_date), HOUR(start_date)
ORDER BY day, hour;

A display label such as 09:00–09:59 can be generated in the presentation layer. Returning a numeric hour keeps ordering and chart construction straightforward.

Removing GROUP BY does not select a reliable date

The earlier example suggested that omitting grouping would put the total beside the first date. That is not reliable. With ONLY_FULL_GROUP_BY enabled, an invalid aggregate query is rejected. With it disabled, the selected date is not guaranteed. Select only COUNT(*) for an overall total, or retain grouping for daily counts. See MySQL's GROUP BY rules.

Empty buckets and reporting time zones

An hour without records does not appear automatically with a zero. For a complete chart, initialize hours 0–23 in the application and fill them from the query, or LEFT JOIN against a calendar/hour table. In the latter case, count a non-null key from the matched table rather than using COUNT(*), which can count the unmatched placeholder row.

TIMESTAMP values are converted according to the session time zone; DATETIME does not receive the same automatic conversion. If values represent UTC, define the reporting zone before computing the boundaries for “today.” In regions with daylight-saving transitions, a local day is not always 24 hours. Consult MySQL time-zone support.

Check the plan as the table grows

A suitable index on start_date can assist range access; inspect the plan with EXPLAIN. The selected range and grouping still have a cost. Bind user-supplied boundaries as parameters in PHP rather than concatenating them into SQL. Include midnight boundaries, fractional seconds and empty hours in your acceptance data before using the report.