SQL query: a list of all identifiers that were active for a given time interval, sorted by the time they were run

Question

I have a MySQL table containing points (x / y coordinates) of tracks. Each line contains TrackID, Timestamp and X and Y Positions for this track at a given time.

What I want is a list of all TrackIDs that were active for a given time interval (tmin ... tmax), sorted by their start time, even if that start time is outside the interval.

A small illustration may help:

As an example: Track 1 is active from t11 to t12, which means that I have many rows in my table with ID = 1 and with timestamps from t11 to t12.

Desired Result:

TrackID | StartTime --------+----------- 7 | t71 1 | t11 2 | t21 6 | t61 

I tried something like this:

 SELECT TrackID, MIN(Timestamp) AS StartTime FROM Tracks WHERE Timestamp BETWEEN tmin AND tmax GROUP BY TrackID ORDER BY StartTime; 

However, in the above example, I do not get real start times for tracks 1 and 7, since all lines with timestamps less than tmin are not taken into account at all.

Of course, in the first step, I could just get all the active TrackIDs using

 SELECT TrackID FROM Tracks WHERE Timestamp BETWEEN tmin AND tmax GROUP BY TrackID; 

and then, with individual queries, find the start time of all these tracks, and then sort them in my application code.

But I'm sure there is a way to do this with a single SQL query. My table contains millions of rows, so efficiency is a problem here.

Answer 1

One way to think about this is to build logic to handle your four special cases in your diagram. These two rules should be sufficient.

• tend> tmin AND
• tstart <Tmax

If either of these two conditions is true, then the track must be enabled. You will need a list of tracks, as in your second query, with their minimum and maximum values, and then make comparisons:

 SELECT T.TrackID FROM (SELECT TrackID, MIN(Timestamp) AS StartTime, MAX(Timestamp) AS EndTime FROM Tracks GROUP BY TrackID) T WHERE T.EndTime > tmin AND T.StartTime < tmax 

Answer 2

Take a look at your image - all the ranges you want have an end time greater than min and a start time less than max .

Answer 3

What about:

 SELECT TrackID, MIN(Timestamp) AS StartTime FROM Tracks WHERE EXISTS( SELECT * FROM Tracks WHERE Timestamp BETWEEN tmin AND tmax) GROUP BY TrackID ORDER BY StartTime; 

This is better, I think:

 SELECT SQUERY.TrackID, MIN(SQUERY.Timestamp) AS StartTime FROM ( SELECT * FROM Tracks WHERE Timestamp BETWEEN tmin AND tmax) AS SQUERY GROUP BY SQUERY.TrackID ORDER BY SQUERY.StartTime; 

Ok, I'm sure it is now: p

 SELECT TrackID, MIN(Timestamp) AS StartTime FROM Tracks WHERE TrackID IN ( SELECT TrackID FROM Tracks WHERE Timestamp BETWEEN tmin AND tmax) GROUP BY TrackID ORDER BY StartTime; 

Answer 4

You were on the right track with your second query, you just need to add the ORDER BY .

 SELECT DISTINCT TrackID FROM Tracks WHERE Timestamp BETWEEN tmin AND tmax GROUP BY TrackID ORDER BY Timestamp;