Get data in current date last hour in sql

Question

i want to count the data in current date last hour (for example now() is 2020-04-11 12:30:00 , then i want to get data before 12. any idea ?

table_a

timestamp           
2020-04-11 10:00:00     
2020-04-11 10:00:00     
2020-04-11 11:00:00     
2020-04-11 12:00:00  
2020-04-11 12:30:00

expected results:

timestamp            count
2020-04-11 10:00:00     2
2020-04-11 11:00:00     1
2020-04-11 12:00:00     1

i'm using mysql 8

Tag your question with the database you are using. – Gordon Linoff Nov 29 '20 at 19:35 — Gordon Linoff, Nov 29 '20 at 19:35

Gordon Linoff · Answer 1 · 2020-11-29T20:53:49.023

1

Based on other questions, it seems like you are using MySQL. So, one method is:

where timestamp >= curdate() and
      hour(timestamp) < hour(now())

edited Nov 29 '20 at 20:53

answered Nov 29 '20 at 19:36

Gordon Linoff

why the output was empty ? – hinafaya Nov 29 '20 at 19:56
I fixed the query. That should be `hour(now())`, not `hour(curdate())`. – Gordon Linoff Nov 29 '20 at 20:54
thank u, its works for me! can u upvote my questions ? – hinafaya Dec 01 '20 at 07:14
@hinafaya Nope. It doesn't work like that. – 10 Rep Dec 02 '20 at 03:57
its work for me with little different query, ` where date(timestamp) >= date(now()) and hour(timestamp) < hour(now()) group by hour(timestamp)` – hinafaya Dec 02 '20 at 08:05
@10Rep u can upvote my questions if its clear – hinafaya Dec 02 '20 at 08:14
1

@hinafaya But you cannot ask that. I don't really think it's clear. I didnt downvote your question, but I didn't upvote either. On the other hand, if Gordon's answer helped you, then please upvote it. Accept the answer you think helped you the most. – 10 Rep Dec 02 '20 at 17:21

score 1 · Answer 2 · answered Nov 30 '20 at 02:46

1

Like Gordon mentioned you need to go with the where clause however looking at your table:

SELECT timestamp, COUNT(*)
FROM table_a
WHERE timestamp >= curdate() and
      hour(timestamp) < hour(now())
Group by timestamp

Should be the complete query.....

answered Nov 30 '20 at 02:46

Yunfei Chen

its also works for me, thankss! can u upvote my questions ? – hinafaya Dec 01 '20 at 07:15
@hinafaya if it works can you accept it, so future readers know that the question has been solved?? – Yunfei Chen Dec 01 '20 at 19:42

2 Answers2