sql - MySQL select rows with timestamp closest to but not exceeding the given timestamp -
i have table looks 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 know states of entities @ given time. example, if timestamp received application 2015-05-15 14:25:00
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, know last state change took place each entity before or @ given time. interval between state change not fixed. cannot have 2 time boundaries , find rows between them.
i have tried using timediff
failed desired output. please guide me on path should take?
edit: quick responses. tried answers , noticed queries take quite lot of time fetch rows when executed on actual database. because fields entity_id
, state_id
foreign keys 2 other tables. known there way improve performance of query?
you can using variables:
select entity_id, state_added_time, state_id ( select state_added_time, state_id, @row_number:= case when @entity = entity_id @row_number+1 else 1 end row_number, @entity:=entity_id entity_id state_history state_added_time <= '2015-05-15 14:25:00' order entity_id, state_added_time desc ) t t.row_number = 1
@row_number
being reset each time new entity_id
encountered. within each entity_id
, value of @row_number = 1
points recent record.
Comments
Post a Comment