Inexact searching for nearest time value

I'm looking for a programmatically and syntactically efficient way to go about searching for a value based on a Timestamp. Essentially I want to find the closest timestamp matc开发者_如何学Goh...

Say for example in a MySQL table I have:

ID    TIME    Blob
1    4:03:10    abc
2    4:04:30    def
3    4:04:45    ghi

And I want to query this table based on the time 4:04:40. I would want to return record ID #3... If I searched for 4:04:35 I would want to return ID #2... How do I go about implementing this? I have many millions of rows in this table and I was thinking something like levenshtein dist will be too slow..?

Cheers!

How about:

SELECT id, MIN(ABS(time(time) - time("4:04:35"))) FROM table

Bear with me, I'm more used to Oracle where dates can be manipulated exactly like numbers if necessary, but I think you should look for the row that has the date with the smallest difference to your search date. The difference could be positive or negative so ABS() will be needed to take care of that.

How about

SELECT table.id, 
       ABS(TIMESTAMPDIFF(SECOND, table.time, [datetime.now])) as difference
FROM table

If that looks OK you then have to select the ID that has the minimum [difference]

SELECT MIN(time) FROM table WHERE TIME > [datetime.now]