How to return some * data with time interval?

Question

How to return some * data with time interval?

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

I have a monitoring system. Retrieves the user's location every 5 seconds. However, I want to show only the records that appear in the range every 15 seconds (for example). The main idea is that it is dynamic, where the user of the system can make this switch, being 10, 15, 20 or 60, etc. One of the filters would be the start date and end date to fetch those records. See the following table below:

+------------+-------------------+
|     ID     |       data        |
+------------+-------------------+
|     1      |2017-07-10 10:11:10|
+------------+-------------------+
|     2      |2017-07-10 10:11:15|
+------------+-------------------+
|     3      |2017-07-10 10:11:20|
+------------+-------------------+
|     4      |2017-07-10 10:11:25|
+------------+-------------------+
|     5      |2017-07-10 10:11:30|
+------------+-------------------+
|     6      |2017-07-10 10:11:35|
+------------+-------------------+
|     7      |2017-07-10 10:11:44|
+------------+-------------------+
|     8      |2017-07-10 10:12:18|
+------------+-------------------+

I would like a query to return only the data that has a difference of 15 seconds (for example) between the previous date. For this example above, I would return the following lines:

+------------+-------------------+
|     ID     |       data        |
+------------+-------------------+
|     1      |2017-07-10 10:11:10|
+------------+-------------------+
|     4      |2017-07-10 10:11:25|
+------------+-------------------+
|     7      |2017-07-10 10:11:40|
+------------+-------------------+
|     8      |2017-07-10 10:12:18|
+------------+-------------------+

I do not want to be returned all database data in a date range , but return only some data by skipping some lines based on a time, for example, 15 seconds.

How would this query be?

mysql

asked by anonymous 31.07.2017 / 15:07

2 answers

Error: Value can not be null. Parameter name: dataTable. VB.NET How to send JSON to PHP?

score 2 · Answer 1

Using CTE this is possible from SQL Server 2005 , is also available on the MYSQL .

But what's even Common Table Expression (CTE)?

A Common Table Expression (CTE) can be seen as a result set which is defined in the execution scope of a single statement SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statements. A CTE is very similar to a derived table that is not stored as an object and which exists only during the execution of the query. Different from one table can be self-referenced and can be referenced multiple times in the same query.

INSERT

INSERT INTO Table_Data(DATA)
VALUES('2017-07-10 10:11:10'),('2017-07-10 10:11:15'),('2017-07-10 10:11:20'), ('2017-07-10 10:11:25'),('2017-07-10 10:11:30'),('2017-07-10 10:11:35'),('2017-07-10 10:11:40');

Query:

WITH DATAS AS
  (SELECT *
   FROM Table_Data
   WHERE DATA = '2017-07-10 10:11:10'
   UNION ALL SELECT TD.Data
   FROM Table_Data TD,
        DATAS D
   WHERE DATEDIFF(SS, D.Data,TD.Data) = 15 )
SELECT *
FROM DATAS

Result for the date '2017-07-10 10:11:10':

2017-07-10 10:11:10.000
2017-07-10 10:11:25.000
2017-07-10 10:11:40.000

Result for the date '2017-07-10 10:11:15':

2017-07-10 10:11:15.000
2017-07-10 10:11:30.000

NOTE: This response works on SQL Server, the DATEDIFF is specific to the MSSQL database, so it is necessary to adapt.

score 0 · Answer 2

You can use the SUBDATE .

SELECT 
    COUNT(primary_key), SUBDATE(data, INTERVAL 15 SECOND)
FROM
    tabela
GROUP BY SUBDATE(data, INTERVAL 15 SECOND)
ORDER BY data DESC

Example apicated in version 5.7. It ran and listed the values with the interval every 15 seconds based on the last record. But the distinction of seconds was not exactly as: 00, 15, 30 because I have not yet had time to format the information output. In the future I will change the question