Result range

Asked

Viewed 141 times

5

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 among these results the time of 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"

The date is every 5 minutes

I tried to do so:

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 is coming.

[EDIT]

I’ve done it 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 per minute.

Now we have to bring it every five minutes

mysql sql select

  • What exactly do you mean by Agora falta trazer de 5 em 5 minutos ? A function that every 5 minutes makes this select or a "grouping" every 5 minutes ? Tried to use something like DATE_ADD(sua_data, INTERVAL 5 minute) ?

  • In the example I put the first time is: 2018-11-13 15:35:59 and the second is 2018-11-13 15:40:07 got it? the equivalent of 5 minutes later

  • When you say "every 5 minutes" does that mean that the beginning of the break should be the moment when the minutes are multiples of 5? Note that in your example the difference between the dates does not reach full 5 minutes, is 00:04:08.

  • You have record every 3 seconds. But do you want to pick up every 5 minutes from a set date? That’s it?

  • I created the example structure on http://www.sqlfiddle.com/#! 9/826e65/1

  • It does not seem to me a situation for using the database to extract this information. Because there are business rules involved. So instead, I would loop using your programming language and extract only the necessary data.

Show 1 more comment

3 answers

3


One way would be to use a variable:

SET @data := (SELECT data FROM tab1 ORDER BY data LIMIT 1);

SELECT * FROM tab1
WHERE data IN (
SELECT 
 (CASE WHEN data >= (SELECT data FROM tab1 WHERE data >= ADDTIME(@data, "00:05:00") LIMIT 1)
  THEN @data := data
  ELSE @data 
 END) AS data_5
FROM tab1 A
ORDER BY data)

I set the variable with the first record of the table, but you can do from a specific date as well:

SET @data := (SELECT data FROM tab1 WHERE data > '2018-11-13 15:45' ORDER BY data LIMIT 1);

What I did in "internal query" was to treat the variable with the CASE and whenever data is greater than or equal to "variable + 5 min" (ADDTIME(@data, "00:05:00")), it updates the variable and brings back the date, if not, it keeps and brings back the already set date.

Then I only made a select bringing these dates to compare. Could use a GROUP BY also.

db-fiddle

1

A simple way to solve this problem would be GROUP BY CAST( DATE_FORMAT(data,'%Y%m%d%H%i') / 5 AS UNSIGNED )

1

I don’t know if I understand the problem correctly, but I understand that every five minutes, no matter where the five-minute break starts, you can do it like this:

SELECT * FROM tab1 
WHERE MOD( MINUTE( data ), 5 ) = 0  -- se o minuto for múltiplo de 5
AND data > '2018-11-13 15:40:07'    -- Se quiser maior que uma data especifica.

See working on db-fiddle

Browser other questions tagged mysql sql select

You are not signed in. Login or sign up in order to post.