How do I, from a column of time values, compare them with the current time and get the closest value after the current time?

Say you have a list of time values in a column in a MySQL database.

Time
-----
10:15 AM
10:35 AM
10:55 AM

And say that right now is 10:27 AM. How would I get the 10:35 AM value specifically in a variable in PHP?

I know that I could get the current time with

$currenttime = time();

and then operate on the time values with

$some_time = strtotime($row['time']);

But how do I get the closest on开发者_高级运维e into that $some_time variable?

---

You want the row with the minimum delta between the recorded time and the reference time where the recorded time is greater than the reference time.

SELECT * FROM SomeTable AS T
 WHERE T.Time = (SELECT MIN(T2.Time) FROM SomeTable AS T2
                  WHERE T2.Time > ?)

The '?' is a placeholder for your reference time.

---

You can get closest time from within your query using TIMESTAMPDIFF:

SELECT
  time, TIMESTAMPDIFF(MINUTE, time, NOW()) AS minutes
ORDER BY
  minutes, time DESC

If you want to select just one tow with closest time:

SELECT
  time, TIMESTAMPDIFF(MINUTE, time, NOW()) AS minutes
ORDER BY
  minutes, time DESC
LIMIT 1

---

Try taking the absolute values of the differences between the current time and possible times. Whichever value is lowest is the closest. The only thing from there you would have to worry about is if a given time is equally close to two times which you can handle in whichever way you please (it seems most reasonable to me to simply round up at that point)

Regards,
Dennis M.