mysql - Get data between 2 days group by with 24 + result

Question

Table name: smart_m

serial  |   Timestamp   |   mac_address |   Temp
1   |   2020-12-30 07:00:00 |   12:34:56:78:00:99   |   21.2
2   |   2020-12-30 07:01:00 |   12:34:56:78:00:99   |   22.2
3   |   2020-12-30 07:02:00 |   12:34:56:78:00:99   |   21.5
4   |   2020-12-30 07:03:00 |   12:34:56:78:00:99   |   21.8
5   |   2020-12-30 07:04:00 |   12:34:56:78:00:99   |   21.2
.
.
.
.
xxxxx   |   2020-12-31 07:00:00 |   12:34:56:78:00:99   |   50.5

so data is feeding in the table every minute.

Requirement: I want 1 data of every hour between 2020-12-31 07:00:00 and 2020-12-30 07:00:00

SELECT * FROM `smart_m` 
WHERE `mac_address` = '12:34:56:78:00:99' 
AND `Timestamp` BETWEEN "2020-12-30 07:00:00" AND "2020-12-31 07:00:00" 
GROUP BY HOUR(`Timestamp`) 
ORDER BY `serial`

I used this query which gives only 24 reading which means it gives between 2020-12-30 07:00:00 to 2020-12-31 06:00:00 instead of 2020-12-31 07:00:00

also, this query is a bit slow too.

Can anyone tell me how to fetch data from the table?

Expected Data is as below:

serial  |   Timestamp   |   mac_address |   Temp
1   |   2020-12-30 07:00:00 |   12:34:56:78:00:99   |   21.2
2   |   2020-12-30 08:00:00 |   12:34:56:78:00:99   |   22.2
3   |   2020-12-30 09:00:00 |   12:34:56:78:00:99   |   21.5
4   |   2020-12-30 10:00:00 |   12:34:56:78:00:99   |   21.8
5   |   2020-12-30 11:00:00 |   12:34:56:78:00:99   |   21.2
.
.
.
.
25   |   2020-12-31 07:00:00 |   12:34:56:78:00:99   |   50.5

Answer 1

You have to use DATE_FORMAT https://www.w3schools.com/sql/func_mysql_date_format.asp

SELECT * FROM `smart_m` 
WHERE `mac_address` = '12:34:56:78:00:99' 
AND `Timestamp` BETWEEN "2020-12-30 07:00:00" AND "2020-12-31 07:00:00" 
group by DATE_FORMAT(`Timestamp`, "%Y %m %d %k")
ORDER BY `serial`