Result interval

3

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.

    
asked by anonymous 14.11.2018 / 19:15

0 answers