CODEWITHSUNDEEP
mysql
Joseph
Joseph

Reputation: 733

MYSQL Find the nearest time from table

I have a table which is consist of time as follow

tblTime

id  | in_time
-------------  
1   | 08:00
2   | 16:00

Now i am have a time value 08:45 and 16:10, and i want to get the ID from tblTime for the nearest time to my time value, so the result will be 1 for 08:45 and 2 for 16:10 in this case.

I tried ABS() function but it seem it is not working at all.

SELECT * FROM tblTime ORDER BY ABS(TIMESTAMP(minute,in_time,'08:45'))
SELECT * FROM tblTime ORDER BY ABS(TIMESTAMP(minute,in_time,'16:10'))

Upvotes: 0

Views: 412

Answers (1)

Blank
Blank

Reputation: 12378

Try to use function TIMEDIFF:

order by abs(time_to_sec(timediff(in_time, '08:45')))
limit 1 -- Of course, if you want nearest one, this line is necessary.

Note: time_to_sec will convet time to secs.

Upvotes: 1

Related Questions