Geek Answers Handbook

Getting hourly records from MySQL

Unfortunately, I'm not very strong when it comes to SQL, and not very strong when it comes to explanation. I will try my best and hope someone can help.

I have a soc_stat table that has many columns. One of them is a timestamp, which is only a timestamp, and the other is date_of_post, which is just a date_format();timestamp that looks like this (example) 2015-08-28 00:18:52

What I would like to do is make a query that returns the number of records per day / hour

Something in accordance with these 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 this for a schedule.

I am currently using the following code to get the last 24 hours from my database

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

Edit

. , . . , , . 0? , , SQL-. , 24- , , .

+4
3

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)


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)
+2

SELECT date(soc_stat.date_of_post) as date,hour(soc_stat.date_of_post) as hour,
count(*) FROM `soc_stat` 
WHERE soc_stat.date_of_post > DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
group by date(soc_stat.date_of_post),hour(soc_stat.date_of_post)
+2

Try the query:

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)
0
source

More articles:


All Articles