How can I join tables based on timestamp ranges?
2 views (last 30 days)
Show older comments
I have three tables of a few million rows each, as follows:
Table A has one row for each of 1 million devices, with a column with a unique identifier for each device.
Table B has one row for each change of state for each device in Table A. This table includes the unique device id, a unique state id for each row, and the timestamp when the state change occured.
Table C is a table of every mouse click for each device in table A. This table includes the unique device id, a unique mouseclick id for each row, and the timestamp of the mouseclick.
It is trivial to join A to B and A to C based on the device id. What I need help with is joining B to C based on not only the device id, but also the timestamp. What i want to know is, for every mouseclick in Table C, what state was that device in. If the mouseclick occured on or after the timestamp for status ”m”, but before the timestamp for status “n”, it would join to status ”m”.
0 Comments
Answers (0)
See Also
Categories
Find more on Tables in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!