Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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: Example query output (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.

share|improve this question
    
Please mention the specific version of MySQL (e.g. the output from SELECT @@version,@@version_comment;) and the data type of the `timestamp` and `timestamp_1` columns. So far, I haven't successfully duplicated this behavior in 5.6.10 or 5.6.13. Which GUI did you use to make the screen shot, and does SHOW WARNINGS; tell you anything immediately after running this query (e.g. "Warning | 1292 | Truncated incorrect time value 'foo'")? What if you run the query and SHOW WARNINGS; from the MySQL command line interface? –  Michael - sqlbot Oct 7 '13 at 23:15
    
Apologies, i'm using MySQL version 5.6.12. screenshot taken from mysql workbench 5.2.47. Show warnings produces a list of Warning|1292|Truncated incorrect time value <x> but i'm not sure why it's only doing that for some results, and not all of them. –  almg Oct 8 '13 at 8:20
    
The warnings are good news, in one sense, because at least that means it isn't just silently failing. What is the data type of those timestamp columns, and what are a couple of examples of the timestamp values mentioned in the warnings as being incorrect? –  Michael - sqlbot Oct 8 '13 at 12:23
    
Thanks for the help. i managed to get this all sorted myself by splitting the timestamp into date and time automatically when the data gets loaded into the table, then using the timediff function in this calculation rather than doing it manually. this is now working well! –  almg Oct 9 '13 at 11:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.