Inexact searching for nearest time value

Question

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 match...

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!

Answer 1

How about:

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