How to determine a gap condition between records?

Asked

Viewed 141 times

6

In the case of records containing date and/or time, how to determine that between the first record returned and the others there is always the same interval? Let’s take this example:

Raw data:

TimeStamp           | Valor

2019-01-04 14:08:07 | 29062
2019-01-04 15:09:09 | 28937
2019-01-04 15:09:10 | 28937
2019-01-04 15:09:20 | 28937
2019-01-04 15:10:21 | 28937
2019-01-04 15:40:21 | 28875
2019-01-04 16:10:21 | 28812
2019-01-04 16:40:21 | 28812
2019-01-04 17:10:21 | 28812
2019-01-04 17:40:21 | 28812
2019-01-04 18:10:21 | 28687
2019-01-04 18:40:21 | 28625

Processed data:

TimeStamp           | Valor

2019-01-04 14:08:07 | 29062
2019-01-04 15:09:09 | 28937
2019-01-04 16:10:21 | 28812
2019-01-04 17:10:21 | 28812
2019-01-04 18:10:21 | 28687

Consider a minimum range of 40 min

mysql sql database

  • What version of MySQL?

  • Version of MYSQL: 5.6.35-81.0

1 answer

3


You can use variables to control which date will be used base.

Schema (Mysql v5.6)

CREATE TABLE tabela (
  data  TIMESTAMP,
  valor INTEGER
);

INSERT INTO tabela(data, valor)
            VALUES('2019-01-04 14:08:07', 29062),
                  ('2019-01-04 15:09:09', 28937),
                  ('2019-01-04 15:09:10', 28937),
                  ('2019-01-04 15:09:20', 28937),
                  ('2019-01-04 15:10:21', 28937),
                  ('2019-01-04 15:40:21', 28875),
                  ('2019-01-04 16:10:21', 28812),
                  ('2019-01-04 16:40:21', 28812),
                  ('2019-01-04 17:10:21', 28812),
                  ('2019-01-04 17:40:21', 28812),
                  ('2019-01-04 18:10:21', 28687),
                  ('2019-01-04 18:40:21', 28625);

Query

SET @data := NULL;

SELECT x.data,
       x.valor
  FROM (
    SELECT @data := CASE
                      WHEN @data IS NULL THEN t.data
                      WHEN t.data > @data + INTERVAL 40 MINUTE THEN t.data
                      ELSE @data
                    END,
           CASE @data
             WHEN t.data THEN 'S'
             ELSE 'N'
           END AS mostrar,
           t.*
      FROM tabela t
      ORDER by t.data) x
   WHERE x.mostrar = 'S'
   ORDER BY x.data;

Resulting in:

| data                | valor |
| ------------------- | ----- |
| 2019-01-04 14:08:07 | 29062 |
| 2019-01-04 15:09:09 | 28937 |
| 2019-01-04 16:10:21 | 28812 |
| 2019-01-04 17:10:21 | 28812 |
| 2019-01-04 18:10:21 | 28687 |

See working on DB Fiddle.

Browser other questions tagged mysql sql database

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