SQL query: list of all IDs that were active during a given time interval, sorted by their start-time

Question

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

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

A little illustration might help:

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

The desired output would be:

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 example above I don't get the real start times for tracks 1 and 7, since all rows with timestamps less than tmin are not considered at all.

Of course I could in a first step just get all active TrackIDs with

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

and then with separate queries find the start times of all these tracks and then sort them in my application code.

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

Answer 1

Take a look at your picture - all the ranges you want have ending time greater than min and starting time less than max.

Answer 2

One way to think about it is to construct the logic to handle your four special cases in your diagram. These two rules should suffice.

1. tend > tmin AND
2. tstart < tmax

If any of these two conditions are true, then the track should be included. You will need a list of tracks as in your second query with their min and max values, and then perform the 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