MySQL select rows with timestamp closest to but not exceeding the given timestamp

Question

I have a table which looks something as below

state_history
+---------------------+-----------+----------------+      +
| state_added_time    | entity_id | state_id       | .... |
+---------------------+-----------+----------------+      |
| 2015-05-15 13:24:22 |         1 |              1 |      |
| 2015-05-15 13:29:44 |         3 |              2 |      |
| 2015-05-15 13:34:26 |         2 |              2 |      |
| 2015-05-15 14:24:28 |         1 |              3 |      |
| 2015-05-15 14:24:30 |         2 |              3 |      |
| 2015-05-15 14:26:32 |         3 |              5 |      |
| 2015-05-15 14:26:34 |         3 |              3 |      |
.......

My intention is to know the states of all entities at any given time. For example, if the timestamp received from the application is 2015-05-15 14:25:00 then the expected output should be:

state_history
+---------------------+-----------+----------------+      +
| state_added_time    | entity_id | state_id       | .... |
+---------------------+-----------+----------------+      |
| 2015-05-15 14:24:28 |         1 |              3 |      |
| 2015-05-15 14:24:30 |         2 |              3 |      |
| 2015-05-15 13:29:44 |         3 |              2 |      |
.......

That is, to know the last state change which took place for each entity before or at the given time. The interval between state change is not fixed. Thus I cannot have 2 time boundaries and find rows between them.

I have tried using TIMEDIFF but failed to get the desired output. Could anyone please guide me on the path I should take?

EDIT: Thanks everyone for the quick responses. I tried the answers and noticed that the queries take quite a lot of time to fetch the rows when executed on the actual database. Probably because the fields entity_id and state_id are foreign keys to two other tables. Now that this is known is there any way to improve the performance of the query?

Answer 1

You can also do it using variables:

SELECT entity_id, state_added_time, state_id
FROM (
  SELECT state_added_time, state_id,
          @row_number:= CASE WHEN @entity = entity_id THEN @row_number+1
                             ELSE 1
                        END AS row_number,
          @entity:=entity_id AS entity_id   
  FROM state_history
  WHERE state_added_time <= '2015-05-15 14:25:00'
ORDER BY entity_id, state_added_time DESC ) t
WHERE t.row_number = 1

@row_number is being reset each time a new entity_id is encountered. Within each entity_id, a value of @row_number = 1 points to the most recent record.

SQL Fiddle Demo

Answer 2

Are you looking for this?

SELECT h.*
  FROM 
(
  SELECT entity_id, MAX(state_added_time) state_added_time
    FROM state_history
   WHERE state_added_time <= '2015-05-15 14:25:00'
  GROUP BY entity_id
) q JOIN state_history h
    ON q.entity_id = h.entity_id
   AND q.state_added_time = h.state_added_time

Output:

|      state_added_time | entity_id | state_id |
|-----------------------|-----------|----------|
| May, 15 2015 13:29:44 |         3 |        2 |
| May, 15 2015 14:24:28 |         1 |        3 |
| May, 15 2015 14:24:30 |         2 |        3 |

Here is a SQLFiddle demo

Answer 3

To do this, you can use some simple aggregation. All you need is the MAX() function to get the largest time for each entity_id, on the condition that it is less than a given timestamp.

Once you have that time for each entity_id, you'll need to join it back to the original table in order to get the state_id value as well. It looks like this:

SELECT s.*
FROM state_history s
JOIN(
  SELECT entity_id, MAX(state_added_time) AS maxTime
  FROM state_history
  WHERE state_added_time < '2015-05-15 14:25:00'
  GROUP BY entity_id) tmp ON tmp.entity_id = s.entity_id AND tmp.maxTime = s.state_added_time;

Here is an SQL Fiddle example.