Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Is it possible to query for all events within 2 minutes of specific events?

I can create a query to return specific events based on some criteria, but is it then possible to query for all events with a timestamp within 2 minutes of each of the events returned from the original query?

For example, say I have the following entries in my database:

SELECT Id,Name,StartDateTime,Length FROM Events ORDER BY StartDateTime DESC;

+--------+--------------+---------------------+--------+
| Id     | Name         | StartDateTime       | Length |
+--------+--------------+---------------------+--------+
| 256779 | Event-256779 | 2024-07-22 16:08:43 |  16.51 |
| 256777 | Event-256777 | 2024-07-22 16:08:32 |  18.16 |
| 256775 | Event-256775 | 2024-07-22 16:05:55 |  10.88 |
| 256772 | Event-256772 | 2024-07-22 16:02:57 |  20.13 |
| 256770 | Event-256770 | 2024-07-22 16:02:51 |  15.73 |
| 256768 | Event-256768 | 2024-07-22 16:02:26 |  23.91 |
| 256767 | Event-256767 | 2024-07-22 16:00:34 |  17.82 |
| 256766 | Event-256766 | 2024-07-22 16:00:31 |   9.99 |
| 256765 | Event-256765 | 2024-07-22 15:59:57 |  19.46 |
| 256760 | Event-256760 | 2024-07-22 15:53:19 |  16.09 |
| 256758 | Event-256758 | 2024-07-22 15:53:15 |  11.56 |
| 256753 | Event-256753 | 2024-07-22 15:38:37 |   8.72 |
| 256745 | Event-256745 | 2024-07-22 15:32:52 |  15.50 |
| 256744 | Event-256744 | 2024-07-22 15:32:51 |   6.51 |
| 256737 | Event-256737 | 2024-07-22 15:11:06 |  19.93 |
| 256729 | Event-256729 | 2024-07-22 15:01:17 |   8.98 |
| 256724 | Event-256724 | 2024-07-22 14:54:34 |   9.45 |
| 256722 | Event-256722 | 2024-07-22 14:52:11 |  10.01 |
| 256721 | Event-256721 | 2024-07-22 14:52:09 |   8.83 |
| 256717 | Event-256717 | 2024-07-22 14:35:07 |  17.93 |
+--------+--------------+---------------------+--------+

I want to query for all events with a Length less than 10, but then also return all of the events that are within 2 minutes (before or after) of the returned events.

The first query would look like this:

SELECT Id,Name,StartDateTime,Length FROM Events WHERE Length < 10 ORDER BY StartDateTime DESC;

+--------+--------------+---------------------+--------+
| Id     | Name         | StartDateTime       | Length |
+--------+--------------+---------------------+--------+
| 256766 | Event-256766 | 2024-07-22 16:00:31 |   9.99 |
| 256753 | Event-256753 | 2024-07-22 15:38:37 |   8.72 |
| 256744 | Event-256744 | 2024-07-22 15:32:51 |   6.51 |
| 256729 | Event-256729 | 2024-07-22 15:01:17 |   8.98 |
| 256724 | Event-256724 | 2024-07-22 14:54:34 |   9.45 |
| 256721 | Event-256721 | 2024-07-22 14:52:09 |   8.83 |
+--------+--------------+---------------------+--------+

The desired query would return:

+--------+--------------+---------------------+--------+
| Id     | Name         | StartDateTime       | Length |
+--------+--------------+---------------------+--------+
| 256767 | Event-256767 | 2024-07-22 16:00:34 |  17.82 |
| 256766 | Event-256766 | 2024-07-22 16:00:31 |   9.99 |
| 256765 | Event-256765 | 2024-07-22 15:59:57 |  19.46 |
| 256753 | Event-256753 | 2024-07-22 15:38:37 |   8.72 |
| 256745 | Event-256745 | 2024-07-22 15:32:52 |  15.50 |
| 256744 | Event-256744 | 2024-07-22 15:32:51 |   6.51 |
| 256729 | Event-256729 | 2024-07-22 15:01:17 |   8.98 |
| 256724 | Event-256724 | 2024-07-22 14:54:34 |   9.45 |
| 256722 | Event-256722 | 2024-07-22 14:52:11 |  10.01 |
| 256721 | Event-256721 | 2024-07-22 14:52:09 |   8.83 |
+--------+--------------+---------------------+--------+
Tags:

2

Answers


  1. 0

    Here, you want to query for all events of Length < 10
    and all of the events that are within 2 minutes (before or after) of the returned events.

    This are all those events within 2min (120 sec), before or after of the events of length < 10.
    enter image description here

    So, your query should be:

    WITH T0 AS (
    SELECT *
    FROM Events 
    WHERE Length < 10 
),
T1 AS (
    SELECT * FROM T0

    UNION 

    SELECT E.*
    FROM Events E, T0
    WHERE (
        (TIME_TO_SEC(TIMEDIFF(E.startdatetime, T0.startdatetime)) + T0.length) < 120
        and TIME_TO_SEC(TIMEDIFF(E.startdatetime, T0.startdatetime)) > 0
    ) OR (
        (TIME_TO_SEC(TIMEDIFF(T0.startdatetime, E.startdatetime)) - T0.length) < 120
        and TIME_TO_SEC(TIMEDIFF(T0.startdatetime, E.startdatetime)) > 0
    )
)
SELECT * FROM T1 
ORDER BY startdatetime DESC;

    Sample query for details view:

    db<>fiddle

    Your actual output is:

    enter image description here

    Added Simplify query according to your requirements:

    -- Final Result

SELECT * FROM Events WHERE Length < 10 

UNION 

SELECT E.*
    FROM Events E, 
      (SELECT * FROM Events WHERE Length < 10) T0
    WHERE (
        (TIME_TO_SEC(TIMEDIFF(E.startdatetime, T0.startdatetime)) + T0.length) < 120
        and TIME_TO_SEC(TIMEDIFF(E.startdatetime, T0.startdatetime)) > 0
    ) OR (
        (TIME_TO_SEC(TIMEDIFF(T0.startdatetime, E.startdatetime)) - T0.length) < 120
        and TIME_TO_SEC(TIMEDIFF(T0.startdatetime, E.startdatetime)) > 0
    )

ORDER BY startdatetime DESC;

    Updated query: db<>fiddle

    Login or Signup to reply.
  2. 0

    You can use an exists query:

    select *
from events
where length < 10
or exists (
    select *
    from events as x
    where length < 10
    and events.startdatetime between startdatetime - interval 2 minute
                                 and startdatetime + interval 2 minute
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search