SQL query: list of all IDs that were active during a given time interval, sorted by their start-time
mysql, sql
Solution
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.
- tend > tmin AND
- 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
Problem
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.