I have a table where records are saved every 3 seconds 24 hours a day
Table: myTable
dado1 dado2 dado3 data
"135,05" "134,69" "135,10" "2018-11-13 15:38:16"
"132,00" "131,91" "132,61" "2018-11-13 15:38:24"
"130,63" "130,57" "131,21" "2018-11-13 15:38:33"
"129,96" "129,92" "130,88" "2018-11-13 15:38:42"
"129,69" "129,55" "130,77" "2018-11-13 15:38:50"
"129,57" "129,38" "130,47" "2018-11-13 15:38:59"
"129,44" "129,27" "130,60" "2018-11-13 15:39:07"
"129,85" "129,72" "130,80" "2018-11-13 15:39:16"
"129,91" "129,74" "131,24" "2018-11-13 15:39:24"
"130,17" "129,83" "131,05" "2018-11-13 15:39:33"
"130,36" "130,00" "131,21" "2018-11-13 15:39:42"
"130,30" "129,97" "131,14" "2018-11-13 15:39:50"
"130,47" "130,00" "131,25" "2018-11-13 15:39:59"
"130,50" "130,02" "131,21" "2018-11-13 15:40:07"
I would like to bring the results to 5 in 5 minutes
For example
"130,47" "130,00" "131,25" "2018-11-13 15:35:59"
"130,50" "130,02" "131,21" "2018-11-13 15:40:07"
Date is every 5 minutes
I tried to do this:
SELECT dado1
,dado2
,dado3
,data
FROM myTable
WHERE data BETWEEN DATE_SUB(DATE(NOW()), INTERVAL DAYOFMONTH(DATE(NOW()))-1 DAY) and last_day(DATE(NOW()))
AND medidor = '62'
GROUP BY MINUTE(data)
HAVING
COUNT(data) <= 5
ORDER BY id
But nothing comes.
[EDIT]
I've already been able to do this here:
SELECT SUBSTRING_INDEX(GROUP_CONCAT(dado1), ',', 1) dado1
,SUBSTRING_INDEX(GROUP_CONCAT(dado2), ',', 1) dado2
,SUBSTRING_INDEX(GROUP_CONCAT(dado3), ',', 1) dado3
,DATE_FORMAT(data,'%Y-%m-%d %H:%i') dia
FROM myTable
WHERE data BETWEEN DATE_SUB(DATE(NOW()), INTERVAL DAYOFMONTH(DATE(NOW()))-1 DAY) and last_day(DATE(NOW()))
AND medidor = '62'
GROUP BY DATE_FORMAT(data,'%Y-%m-%d %H:%i')
ORDER BY id
Which brings the following data
"135.05" "134.69" "135.10" "2018-11-13 15:38"
"130.47" "130.00" "131.25" "2018-11-13 15:39"
"130.17" "129.83" "131.05" "2018-11-13 15:40"
"129.64" "129.57" "130.77" "2018-11-13 15:41"
"129.17" "129.08" "130.19" "2018-11-13 15:42"
"129.61" "129.36" "130.55" "2018-11-13 15:43"
"129.28" "129.22" "130.53" "2018-11-13 15:44"
"129.71" "129.64" "130.69" "2018-11-13 15:45"
"129.83" "129.82" "130.77" "2018-11-13 15:46"
"130.22" "130.03" "131.24" "2018-11-13 15:47"
"128.88" "128.83" "129.92" "2018-11-13 15:48"
"128.75" "128.75" "129.92" "2018-11-13 15:49"
"128.41" "128.38" "129.63" "2018-11-13 15:50"
"129.32" "129.22" "130.25" "2018-11-13 15:51"
That is, the data of the rows grouped by the minute.
Now it's time to bring it in every 5 minutes.