How to return some* data with time interval?

Asked

Viewed 1,201 times

2

I have a monitoring system. Recovers user location every 5 seconds. However, I want to show only the records that appear in the interval every 15 seconds (for example). The main idea is that it is dynamic, where the system user can make this alternation, being 10, 15, 20 or 60, etc. One of the filters would be the start date and end date to search for these 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 that returns only the data that has difference of 15 seconds (for example) between the previous date. For this example above, return me 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 don’t want you to call me back all database data in a date range, but return only some data by skipping a few lines based on a time, for example, 15 seconds.

What would that be like query?

mysql

  • You part of some data?

  • @Marconi did not understand your doubt.

  • I say because of 2017-07-10 10:11:15 for 2017-07-10 10:11:30 would have 15 seconds interval too.

  • @Marconi actually, starting from the last record for example.

  • Be able to do in SQL-Server from the Initial Date = 2017-07-10 10:11:10, help you @acklay?

  • 1

    @Marconi if you have something that you don’t have in Mysql I can take a look at how to adapt it. Probably, I say it probably does. Send ai. = D

  • Posted, I believe that just adjust the function DATEDIFF @acklay :)

Show 2 more comments

2 answers

2

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

But what turns out to be Common Table Expression (CTE) ?

A Common Table Expression (CTE) can be viewed as a result set which is defined in the scope of execution of a single instruction SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW. 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 consultation. Different from a derived table a CTE can be self-referenced and can be referenced several 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 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 date '2017-07-10 10:11:15':

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

NOTE: This answer works on SQL-Server, the function DATEDIFF is specified from the MSSQL database, so it is necessary to adapt.

  • Vish, I am using Mysql 5.7... it seems that CTE has for the 8.0... I am analyzing here to see if it is possible to adapt. I did a test here, I have 10mil lines. Maybe I will give a lock using this approach. Locally lasted 3.5784 seconds to return the results.

  • Vish, I didn’t know @acklay, even I don’t have MYSQL installed. I hope it works there :)

  • @acklay the answer below meets?

  • Are you saying Djalma’s answer?! It didn’t work for me here with me.

  • @acklay yes, from what I could tell it wouldn’t work at all. I think you’ll need something recursive msm. Or else take to the side of the application and adjust there.

  • 1

    I asked a question in Sozão. There was an answer, but it didn’t hurt either. https://stackoverflow.com/questions/45419436/how-to-return-some-data-with-time-interval

  • The guy made a Join and uses timestampdiff, which has the same purpose as SQL DATEDIFF.

  • Got it, top question yours, pity that my answer can not help. But already finds a solution

  • I will edit and put the beginning of it as it is there in Sozão. But thanks for the strength +1.

  • 1

    I asked a question there in the DBA OS also: https://dba.stackexchange.com/questions/182549/how-to-return-some-data-with-time-interval ... still nothing of a concrete solution. I’m still doing some tests.

  • @acklay what big trouble you got into.

  • @acklay why not do in the application?

  • Because in my head, doing it on the bench would be much easier. I still hadn’t thought about doing it on the application. I’m doing some tests. I created a temporary solution using mod. = D

  • I get it, post the result so we can see if we find something =D

Show 9 more comments

0

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

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

  • Did you test this? It didn’t work with me here. And I also need the Ids listed in the query. This way it does not display.

  • I changed the question, as your comment

Browser other questions tagged mysql

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