CODEWITHSUNDEEP
mysql
abalest14
abalest14

Reputation: 33

mysql select records with timestamp distance of 3 minute

i have a table structure like this:

CREATE TABLE `trackings` (
  `id` bigint(20) NOT NULL,
  `latitude` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `longitude` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `trackings` (`id`, `latitude`, `longitude`, `created_at`) VALUES
(171, '45.7006124', '9.1402032', '2020-12-08 07:48:04'),
(172, '45.7008048', '9.1407833', '2020-12-08 07:49:09'),
(173, '45.7009405', '9.1418868', '2020-12-08 07:50:16'),
(174, '45.7009405', '9.1418868', '2020-12-08 07:51:16'),
(175, '45.7010856', '9.14373', '2020-12-08 07:52:26'),
(176, '45.7011537', '9.1446661', '2020-12-08 07:53:31'),
(177, '45.7012833', '9.1465588', '2020-12-08 07:54:41'),
(178, '45.70119', '9.1485754', '2020-12-08 07:55:51'),
(179, '45.7010545', '9.1507779', '2020-12-08 07:56:01'),
(180, '45.7013211', '9.1530207', '2020-12-08 07:57:11'),
(181, '45.7015063', '9.1551616', '2020-12-08 07:57:21'),
(182, '45.7016013', '9.1573392', '2020-12-08 07:57:31');

from this set of data i need a select to extract record each 3 minute to achieve this result:

171, '45.7006124', '9.1402032', '2020-12-08 07:48:04'
174, '45.7009405', '9.1418868', '2020-12-08 07:51:16'
177, '45.7012833', '9.1465588', '2020-12-08 07:54:41'
180, '45.7013211', '9.1530207', '2020-12-08 07:57:11'

The set of data it is only a semplify example, the data is recorded each 5-10 second and there is thousands of record per day.

I don't know the sql approch to achieve it with best performance. Thanks

Upvotes: 1

Views: 99

Answers (2)

id'7238
id'7238

Reputation: 2593

In MySQL 8 you can use window functions.

SELECT id, latitude, longitude, created_at
FROM (
  SELECT *,
    TIMESTAMPDIFF(SECOND, FIRST_VALUE(created_at) OVER w, created_at) AS sec,
    TIMESTAMPDIFF(SECOND, FIRST_VALUE(created_at) OVER w, LAG(created_at) OVER w) AS prev_sec
  FROM trackings
  WINDOW w AS (ORDER BY created_at)
) t
 WHERE COALESCE(prev_sec % 180, 0) >= sec % 180

In this example, sec is the difference between the first and current record in seconds, and prev_sec is the difference between the first and the previous one. Then the remainder of the division by 180 is compared.

db<>fiddle

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

Depending on your precise definition of '3 minutes', here's one idea...

SELECT a.* 
  FROM trackings a
  JOIN 
     ( SELECT MIN(id) id
            , FLOOR(TIME_TO_SEC(TIMEDIFF(x.created_at,y.created_at))/180)*180 delta 
         FROM trackings x 
         JOIN  
             ( SELECT MIN(created_at) created_at 
                 FROM trackings
             ) y
         GROUP
             BY delta
     ) b
    ON b.id = a.id;

Upvotes: 1

Related Questions