Andreas Uldall Leonhard Andreas Uldall Leonhard - 5 months ago 10
MySQL Question

Fetching entries by hours from MySQL

Unfortunately I am not very strong when it comes to SQL and not very strong when it comes to explaining myself. I will try my best and hopefully someone can help.

I have a table called soc_stat which has a lot of colums. One of those is "timestamp" which is just a timestamp, and the other one is a date_of_post which is just a

date_format();
of the timestamp, looking like this (example) 2015-08-28 00:18:52

What I would love to do it make a query that returns the amount of entries per day/hour

Something along the lines of this examples

2015-08-28 00:01:00 = 218 entries
2015-08-28 00:02:00 = 327 entries
2015-08-28 00:03:00 = 487 entries
2015-08-28 00:04:00 = 118 entries


I need it for a graph.

I currently use the following code to get the last 24 hours out from my database

SELECT * FROM `soc_stat` WHERE soc_stat.date_of_post > DATE_SUB(CURDATE(), INTERVAL 1 DAY)


Edit

Thanks a lot for the answers. They worked exactly like how I wanted the information out. However I got one more question. As it is now if there is no inputs within an hour or day, there is not returned anything. Is is possible to get a 0 returned? I have a graph that depends on the values I get from the SQL query. So if I want a 24 hour graph some hours are not going to be displayed if there is no input.

Answer

Try:

SELECT date(t.date_of_post) AS date,
       hour(t.date_of_post) as hour,
       count(*) as entries
FROM `soc_stat` t
WHERE t.date_of_post > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY date(t.date_of_post), hour(t.date_of_post)

OR:

SELECT  DATE_ADD(date(t.date_of_post), INTERVAL hour(t.date_of_post)  HOUR)  AS dateTime,
       count(*) as entries
FROM `soc_stat` t
WHERE t.date_of_post > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY date(t.date_of_post), hour(t.date_of_post)