试图从SQL获取24小时的数据

I am not very skilled at SQL so hopefully someone here can help me out.

我不太擅长SQL，所以希望这里有人能帮助我。

I have a date_of_post column in my table which looks like this (example) 2015-08-31 11:00:00.

我的表中有一个date_of_post列，它类似于(示例)2015-08-31 11:00:00。

I use the INTERVAL 1 DAY to get the last 24 hours. However it returns more than the last 24 hours it seems. This is the query I use to fetch my data

我用间隔1天得到最后24小时。然而，它的回报率似乎比过去24小时还要高。这是我用来获取数据的查询

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 `main_tag` = 'morgenmad' 
  AND t.date_of_post > DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
GROUP BY date(t.date_of_post), hour(t.date_of_post)

And it returns the following:

它返回如下:

2015-08-31 11:00:00 = 11
2015-08-31 12:00:00 = 2
2015-08-31 13:00:00 = 3
2015-08-31 14:00:00 = 3
2015-08-31 15:00:00 = 1
2015-08-31 16:00:00 = 3
2015-08-31 17:00:00 = 2
2015-08-31 19:00:00 = 1
2015-09-01 04:00:00 = 1
2015-09-01 05:00:00 = 3
2015-09-01 06:00:00 = 9
2015-09-01 07:00:00 = 33
2015-09-01 08:00:00 = 38
2015-09-01 09:00:00 = 29
2015-09-01 10:00:00 = 13
2015-09-01 11:00:00 = 12
2015-09-01 12:00:00 = 6
2015-09-01 13:00:00 = 5

I don't understand why 11:00:00, 12:00:00 and 13:00:00 exist in 2015-08-31 and 2015-09-01. Shouldn't it only return the last 24 hours?

我不明白为什么在2015-08-31和2015-09-01中会有11:00:00,12:00:00和13:00:00。难道它不应该只返回最后的24小时吗?

2 个解决方案

#1

CURDATE() returns the "beginning of today". replace it with NOW()

CURDATE()返回“今天的开始”。换成现在()

#2

A visual might help. If you use aliases stick with them throughout. When you use aggregate functions like count, group by all non-aggregate columns.

视觉可能会有所帮助。如果使用别名，则始终使用别名。当您使用聚合函数如count时，将所有非聚合列分组。

for me it is 2015-09-01 08:47:00

create table soc_stat
(   id int auto_increment primary key,
    main_tag varchar(20) not null,
    date_of_post datetime not null
);
truncate table soc_stat;
insert soc_stat (main_tag,date_of_post) values  ('morgenmad','2015-09-02 11:00:00');
insert soc_stat (main_tag,date_of_post) values  ('morgenmad','2015-09-01 11:00:00');
insert soc_stat (main_tag,date_of_post) values  ('morgenmad','2015-09-01 09:00:00');
insert soc_stat (main_tag,date_of_post) values  ('morgenmad','2015-09-01 08:00:00');
insert soc_stat (main_tag,date_of_post) values  ('morgenmad','2015-09-01 07:00:00');
insert soc_stat (main_tag,date_of_post) values  ('morgenmad','2015-08-31 09:00:00');
insert soc_stat (main_tag,date_of_post) values  ('morgenmad','2015-08-31 08:00:00');
insert soc_stat (main_tag,date_of_post) values  ('morgenmad','2015-08-31 07:00:00');

SELECT date(t.date_of_post) dt, hour(t.date_of_post) hr,count(*) as entries 
FROM `soc_stat` t  
WHERE t.`main_tag` = 'morgenmad'  
AND t.date_of_post between DATE_SUB(now(), INTERVAL 1 DAY) and now() 
GROUP BY dt,hr 
order by t.date_of_post desc;

+------------+------+---------+
| dt         | hr   | entries |
+------------+------+---------+
| 2015-09-01 |    8 |       1 |
| 2015-09-01 |    7 |       1 |
| 2015-08-31 |    9 |       1 |
+------------+------+---------+

#1