MySQL Record Count by Date

Hello again. In the system being used, it was requested to add 2 new pages to the reports. One of them is the daily message count, and the other is the hourly message count for a specific date. This is to be done with PHP, but first, we will write the SQL query to display the data. Since this data isn't written anywhere, we need to fetch it from the main table via SQL query. I will share the SQL codes with you as they may be useful for you in the future.

Our Database: MySQL 5.X

Our Table: ChatMaster

Date Column: start_date

Bad Situation: The values in the start_date column are in date-time format (e.g., 2018-09-01 12:34:56).

First, we will fetch the records after September 1st, 2018, on a daily basis. This will give us the daily message count.

SELECT DATE(start_date) TARIH, COUNT(*) ADET FROM ChatMaster 
WHERE start_date>='2018-09-01 00:00:00' GROUP BY DATE(start_date)

So, what does this query mean? Since I need data based on dates, I used SELECT with DATE(start_date) to only focus on the date part of the start_date column, and I assigned it the name TARIH (you can also use *as* if you prefer). To count the entries, I used COUNT. Since my condition is to work with records from September 1st, 2018, I used WHERE to specify this. Then, I used GROUP BY to group the data, so it doesn't sum everything and just put the total in the first date. The result:

What would happen if I didn’t use GROUP BY? The result would be as follows:

Everything so far is fine. Now, how do we get the hourly data for the previous day? We already know the previous query. Let’s modify this query to fetch hourly data. To do this, we simply need to replace DATE with HOUR.

SELECT HOUR(start_date) SAAT, COUNT(*) ADET FROM ChatMaster
WHERE start_date>='2018-09-11 00:00:00' and start_date<='2018-09-11 23:59:59' 
GROUP BY HOUR(start_date)

Look at the result, it looks so good, doesn’t it?

The table looks amazing. Well, let’s use CONCAT to make it even better.

SELECT CONCAT( HOUR(start_date), ':00 - ', CONCAT( HOUR(start_date), ':59' ) ) SAAT, 
COUNT(*) ADET FROM ChatMaster
WHERE start_date>='2018-09-11 00:00:00' and start_date<='2018-09-11 23:59:59' 
GROUP BY HOUR(start_date)

Check the result, it looks much better now, right?

Everything is simple now. Just take this SQL query and use it inside your PHP code.

Goodbye.

If you have any additional comments or questions, feel free to leave them. I will try to respond as best as I can.