MYSQL: Return column time value based on current time?

Question

OK I have a database with a table containing four columns. Each column has many records all with times of day entered with the mySQL TIME data type.

How do I go about querying the database and returning the time value nearest to the current time of day please?

I have it set up so that I have a simple webform that takes a user input of which column the user wants. I want the TIME value nearest to the current time from the chosen column returned to the user.

COLUMN1 COLUMN2 COLUMN3 COLUMN4
11:00   11:40   11:25   11:35
12:05   12:25   12:35   12:25

etc

So if the user enters "3" into the webform and the current time is 12:10 I'd like the 12:35 value returned to them.

I have been Googling and I believe I need to use CURTIME(). I don't want an answer for how to take the user input and use it to query which column. I just need to know how to return a column's time value closest to the current time please! Thank you.

Are the records in any particular order? i.e most recent time first/last? — user1849060, May 24 '15 at 10:20
[SQL Query to show nearest date?](http://stackoverflow.com/questions/6186962/sql-query-to-show-nearest-date) — Federico Biccheddu, May 24 '15 at 10:22

Per76 · Accepted Answer · 2015-05-24T10:36:34.283

3

Try out:

SELECT * 
FROM table_name 
WHERE time > CURTIME()
ORDER BY time ASC 
LIMIT 1

EDIT:

Maybe this is the way to go

SELECT * FROM table_name AS T
 WHERE T.time = (SELECT MIN(T2.time) FROM table_name AS T2
                  WHERE T2.time > ?)

The '?' is a placeholder for your reference time (by PHP).

edited May 24 '15 at 10:36

answered May 24 '15 at 10:24

Per76

184
1
1
15

Yesssss!! works great!! Although I had to change ORDER to ORDER BY....thank you sir! – VanZan May 24 '15 at 10:36
You are most welcome! I edited the SQL (ORDER -> ORDER BY). Have a nice day. – Per76 May 24 '15 at 10:40

score 1 · Answer 2 · answered May 24 '15 at 10:40

You could do:

SELECT column3, TIMEDIFF(column3, CURTIME()) AS difference 
FROM timetbl 
ORDER BY ABS(difference) ASC 
LIMIT 1

If you would need only closest times that are in the future (like when a user needs to catch a train), difference would need to be greater than 0, so you'd add WHERE TIMEDIFF(column3, CURTIME()) > '00:00'

score 0 · Answer 3 · answered May 24 '15 at 10:29

0

Try this. it selects the last entry of time:

SELECT col1 FROM table_name order by col1 desc limit 1;

answered May 24 '15 at 10:29

Abhishek Ginani

4,511
4
23
35

MYSQL: Return column time value based on current time?

3 Answers3