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 and every 15 seconds. Maybe I'll choose a start date and an end date to show these values.... I'm trying to make this filter and I'm not getting it.

I have the following table below:

+------------+-------------------+
|     ID     |       date_       |
+------------+-------------------+
|     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:40|
+------------+-------------------+
|     8      |2017-07-10 10:11:59|
+------------+-------------------+

I would like a * query * that returns me only the data of a difference of 15 seconds. For this example above, I would return the following lines:

+------------+-------------------+
|     ID     |       date_       |
+------------+-------------------+
|     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:11:59|
+------------+-------------------+ 

I want to return only some data by ignoring some rows based on an hour, for example, 15 seconds. This based on the last registered date, for example: 2017-07-10 10:11:40.

How to return some * data with time interval?

Obs.: I'm sorry for English.

closed as unclear what you're asking by Lennart, hot2use, RDFozz, McNets, Erik Darling Aug 3 '17 at 15:52

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Is your date_ column value always has exact 5 seconds interval? Can there be gaps? Can you have 2 entries for same time? – SqlWorldWide Aug 3 '17 at 1:04
  • @SqlWorldWide They are not accurate within 5 seconds. There may be a gap. And there may be more than one entry for the same time. =/ – viana Aug 3 '17 at 13:03
  • Do you want the min time for each 15 sec interval? – Lennart Aug 3 '17 at 13:08
  • @Lennart I want you to return all values with interval between about 15 seconds. But this value of 15 seconds, I want it to be dinamic, it may be 18, 30, 57, etc. (Which is reset by the user). Stefan's solution did not work for me. Maybe I've gotten it wrong in expressing myself. – viana Aug 3 '17 at 13:16
  • Never mind what constant, do you want the min time for every x seconds? – Lennart Aug 3 '17 at 13:18
0

Maybe a combination of the functions SECOND() and MOD() will help. Using your test data as follows (dbfiddle here, also tested with MySQL 5.7):

create table t_ (
  id_ int primary key
, date_ datetime
);

insert into t_ values
(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:40');

We can get the "second" value of the date_, for every 15th second, like so:

select second(date_) 
from t_
where mod( second( date_ ), 15) = 0 ;

-- output
+---------------+
| second(date_) |
+---------------+
|            15 |
|            30 |
+---------------+

The SECOND() value of the "last registered date" would be ...

select second( max( date_) ) from t_ ;

-- output
+-----------------------+
| second( max( date_) ) |
+-----------------------+
|                    40 |
+-----------------------+

Take the "last registered date" into account:

select *
from t_
where mod ( 
  ( select 60 - second( max(date_) ) from t_ ) 
  + second( date_ )
  , 15
) = 0 ;

-- (your required) output
+-----+---------------------+
| id_ | date_               |
+-----+---------------------+
|   1 | 2017-07-10 10:11:10 |
|   4 | 2017-07-10 10:11:25 |
|   7 | 2017-07-10 10:11:40 |
+-----+---------------------+

This query should also work if there are "gaps" in the date_ (the MOD() function does not care) eg if we DELETE everything from the table t_ and insert the following rows:

-- IDs 5 (10:11:20) and 11 (10:11:50) missing
insert into t_ values
(1,'2017-07-10 10:11:00'), (2,'2017-07-10 10:11:05'),
(3,'2017-07-10 10:11:10'), (4,'2017-07-10 10:11:15'),
                           (6,'2017-07-10 10:11:25'),
(7,'2017-07-10 10:11:30'), (8,'2017-07-10 10:11:35'),
(9,'2017-07-10 10:11:40'), (10,'2017-07-10 10:11:45'),
                           (12,'2017-07-10 10:11:55'),
(13,'2017-07-10 10:12:00'),(14,'2017-07-10 10:12:05');

Our query will return:

mysql> select *
    -> from t_
    -> where mod ( 
    ->   ( select 60 - second( max(date_) ) from t_ ) 
    ->   + second( date_ )
    ->   , 15
    -> ) = 0 ;
+-----+---------------------+
| id_ | date_               |
+-----+---------------------+
|   2 | 2017-07-10 10:11:05 |
|   8 | 2017-07-10 10:11:35 |
|  14 | 2017-07-10 10:12:05 |
+-----+---------------------+

If there are (date/time) duplicates, then ... you will see them in the output. You can add a BETWEEN clause (as @Jess suggested) at the end of the query, if you need a "master interval".

UPDATE It seems that you also want to capture values that are smaller/greater than the (exact multiples of) 15 seconds - you have mentioned 18,32, and 57 (seconds), respectively. With a slightly modified query, you can pick up these values, too. Eg the following query has a "tolerance" of +/- 3 seconds eg if the "exact" second is 15, we'd also get 12,13,14,16,17,18. Notice that this "frame" moves according to the MAX() value ie your "last registered" time.

select *
from t_
where mod ( 
  ( select 60 - second( max(date_) ) from t_ ) 
  + second( date_ )
  , 15
) in (0,1,2,3,12,13,14) ;

2 sets of test data (dbfiddle): "last registered" near the full minute , "last registered" at the 30 second mark

  • Morning... tks but... I did a test with 28 seconds and it did not work. In the system, this time will be readjusted. Considered 15 seconds, if the time is 03:15 and the next record is 03:32, that means it will have 17 seconds, which should also return as a result. Your solution does not give me that. – viana Aug 3 '17 at 12:56
  • @acklay: see update. – stefan Aug 4 '17 at 14:16
-3

Maybe try

SELECT * FROM tablename
 WHERE date_ BETWEEN '2017-07-10 8:19:00' AND '2017-07-10 8:19:59'
  • I think you did not understand. Is not this. – viana Aug 3 '17 at 1:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.