I'm working on a program that will calculate journey times based on bluetooth MAC addresses and i'm trying to nail down the mySQL commands I need before I get cracking on the software part of it.
i have the following "rough & dirty" command below which pretty much does what I need:
select *, time(bluetooth_2_maxrssi.timestamp) as "time_1", time(bluetooth_1_maxrssi.timestamp_1) as "time_2" , time(time(bluetooth_2_maxrssi.timestamp)-time (bluetooth_1_maxrssi.timestamp_1)) as "JT1", time(time(bluetooth_1_maxrssi.timestamp_1)-time (bluetooth_2_maxrssi.timestamp)) as "JT2", if(time(time(bluetooth_2_maxrssi.timestamp)-time (bluetooth_1_maxrssi.timestamp_1)) like "%-%","670->671","671->670") as "direction" from bluetooth_1_maxrssi inner join bluetooth_2_maxrssi on bluetooth_1_maxrssi.MAC_1=bluetooth_2_maxrssi.MAC where bluetooth_1_maxrssi.timestamp_1 like "%2013-03-25%" and bluetooth_2_maxrssi.timestamp like "%2013-03-25%"
which produces a set of results for a single day in the format shown in this picture: (This table is just data table for site 2 bolted onto the side of the data table for site 1, then the calculation is done in some extra columns.)
The problem is, as you can see, there are a quite a few null entries in these results (JT1 & JT2). When extended through to the entire amounts to roughly half of the table having null results when there should be a value calculated.
Does anybody know what is going wrong? it's very likely that i am taking the completely wrong approach to this, or there is a much simpler and easier way to go about doing this.
any and all assistance is very much appreciated.