Select + - 2 minutes using time format ()
I'm trying to pick a non-exact arrival time from my arrival time table, but - + 2 minutes with this query:
SELECT route from arrivaltimes INNER JOIN stops
ON arrivaltimes.stop_id=stops.stop_id
WHERE weekday = ?
and time_format(arrivaltime,'%H:%i')= time_format(curtime() ,'%H:%i')
and name LIKE ?
How can I choose + - 2 minutes?
+3
source to share
2 answers
If the field arrival time is a field TIME
, this should work:
SELECT route
FROM arrivaltimes
INNER JOIN stops ON (arrivaltimes.stop_id = stops.stop_id)
WHERE
weekday = ?
AND arrivaltime >= curtime() - INTERVAL 2 MINUTE
AND arrivaltime <= curtime() + INTERVAL 2 MINUTE
AND name LIKE ?
Or you can use the BETWEEN operator :
SELECT route
FROM arrivaltimes
INNER JOIN stops ON (arrivaltimes.stop_id = stops.stop_id)
WHERE
weekday = ?
AND arrivaltime BETWEEN curtime() - INTERVAL 2 MINUTE AND curtime() + INTERVAL 2 MINUTE
AND name LIKE ?
+2
source to share
You can just use BETWEEN....AND
and DATE_ADD
:
SELECT route
from arrivaltimes
INNER JOIN stops ON arrivaltimes.stop_id=stops.stop_id
WHERE weekday = ?
and time_format(arrivaltime,'%H:%i') BETWEEN
time_format(DATE_ADD(curtime,INTERVAL -2 MINUTE) ,'%H:%i') AND
time_format(DATE_ADD(curtime,INTERVAL 2 MINUTE) ,'%H:%i')
and name LIKE ?
+1
source to share