StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL query to get only the start & end time at an event
text
Body
copied!<p>I have two tables:</p> <ul> <li>Locations - stores a user's geographical location and the timestamp.</li> <li>Events - stores an event's geographical bounds and the start and end time of a rock concert event.</li> </ul> <p>Is there a query that will get the start and end time of all users at events?</p> <p>Here's a sample of the data:</p> <pre><code>CREATE TABLE locations ( user_id INT NOT NULL, timestamp DATETIME NOT NULL, latitude FLOAT NOT NULL, longitude FLOAT NOT NULL ); INSERT INTO locations (user_id, timestamp, latitude, longitude) VALUES (1, '2013-11-22 01:12:23', 37.7674, -122.439), (1, '2013-11-22 01:13:24', 37, -122), (1, '2013-11-22 01:14:25', 37.7674, -122.439), (2, '2013-11-25 01:12:23', 37.7674, -122.439), (2, '2013-11-25 01:13:24', 37, -122), (2, '2013-11-25 01:14:25', 37.7674, -122.439); CREATE TABLE events ( event_id INT NOT NULL, begin_time DATETIME NOT NULL, end_time DATETIME NOT NULL, min_latitude FLOAT NOT NULL, max_latitude FLOAT NOT NULL, min_longitude FLOAT NOT NULL, max_longitude FLOAT NOT NULL ); INSERT INTO events (event_id, begin_time, end_time, min_latitude, max_latitude, min_longitude, max_longitude) VALUES (1, '2013-11-22 01:00:00', '2013-11-22 02:00:00', 37.7673, 37.7675, -122.440, -122.439), (2, '2013-11-25 01:00:00', '2013-11-25 02:00:00', 37.7674, 37.7674, -122.439, -122.439); </code></pre> <p>The problem has two parts: </p> <ul> <li>The first part involves finding all the rows where users were at events. </li> <li>The second part involves finding the first timestamp where the user entered the event (then ignore all the subsequent rows where the user remains at the event) then get the row where the user left the event. This would be easy if we just get all rows where the user was inside the event.</li> </ul> <p>The following would give me all rows where the user's location coincides with an event.</p> <pre><code>select * from locations join events on locations.timestamp between events.begin_time and events.end_time and locations.latitude between events.min_latitude and events.max_latitude and locations.longitude between events.min_longitude and events.max_longitude </code></pre> <p>However, I can't seem to figure out a good way to get <em>only the begin and end times</em> of a user at an event. Also, (I don't know if this makes the problem more interesting) it's possible for users to leave and return to the same event.</p> <p>(I'm using MySQL, but I'll accept answers in any flavor of SQL.)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload