Получение данных между 2 днями группирования с результатом 24

#mysql

Вопрос:

Имя таблицы: 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

таким образом, данные поступают в таблицу каждую минуту.

Требование: мне нужны 1 данные за каждый час между 2020-12-31 07:00:00 и 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`

Я использовал этот запрос, который дает только 24 чтения, что означает, что он дает между 2020-12-30 07:00:00 и 2020-12-31 06:00:00 вместо 2020-12-31 07:00:00

кроме того, этот запрос тоже немного медленный.

Кто-нибудь может сказать мне, как извлекать данные из таблицы?

Ожидаемые данные, как показано ниже:

 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

1. Почему вы ожидаете чего-то другого, кроме 24 показаний? У вас есть 24-часовое окно, и вы группируете по часам. Если вы хотите включить 2020-12-30 07:00:00 , это не должно быть вашим пределом.

2. Привет, @El_Vanja Спасибо за ваш ответ. Я не очень разбираюсь в MySQL. Пожалуйста, укажите, что я должен делать. Если возможно, запрос?

3. Попробуйте GROUP BY timestamp только с. При GROUP BY HOUR(timestamp) этом он будет группироваться по часам и игнорировать даты. Таким образом, из 2020-12-31 07:00:00 этого следует 07:00:00 , что предыдущая дата 2020-12-30 07:00:00 также будет иметь то же значение часа.

4. Для решения проблемы с медленным запросом вы можете включить свой план выполнения запроса в свой вопрос. Я предполагаю, что ваши mac_address timestamp столбцы and не индексируются.

5. @tcadidot0 пожалуйста, посмотрите обновленный ожидаемый результат. Я попробовал ваше предложение, и оно дает все данные между указанными датой и временем.

Ответ №1:

Вы должны использовать 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`

Ответ №2:

РЕДАКТИРОВАТЬ: если подумать, @Strawberry прав, и я продолжал думать, что в вашем запросе было что-то странное. В вашем не было агрегации SELECT , поэтому, возможно, то, что вы действительно хотите, это просто получить всю временную метку на часовой отметке. Если это так, вы меняете условие с GROUP BY на WHERE вместо:

 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" 
AND `Timestamp` = REPLACE(`Timestamp`, DATE_FORMAT(`Timestamp`, '%i:%s'),'00:00') 
ORDER BY `serial`;

Используйте DATE_FORMAT для получения значения минут и секунд и REPLACE его с 00:00 помощью .

Демонстрационная скрипка