I'm building a system that queries a table of GPS coordinates and timestamps. The end goal is to deliver the user with a list of GPS points that were logged an hour before or an hour after of their arrival at a certain point.
Right now I bring an array of nearby points out of the databse like so and then I do logic on them by comparing these nearby points to one I preselected before
$getnear = mysql_query("SELECT id, latitude, longitude, SQRT(
POW(69.1 * (latitude - $latitude), 2) +
POW(69.1 * ($longitude - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM geolocalQ HAVING distance < 0.0311 ORDER BY distance") or die(mysql_error());
$neartable = array();
while($row = mysql_fetch_assoc($getnear)) {
$neartable[$row['id']] = $row['id'];
}
I'd like to be able to run if logic for each element of this array based on the time. So something like:
$time=abs($querytime-$mytime)
if ($time<=1 hour) {
#Perform action
}
How do I add, subtract and logically compare timestamps from my database in this format 2013-01-13 18:07:54 The date isn't really important and all calculations are done before the end of each day so in theory I could store the data in minutes after 12am. So 1am would be stored as 60, 2:30 as 150, etc... Thoughts?
Looks like you can have the database do the datetime operations and filter against the results.
These functions should help for MySQL.