Clever Geek Handbook

MYSQL: return column time value based on current time?

OK I have a database with a table that contains four columns. Each column contains many records with the time of day entered with the data type mySQL TIME.

How do I access the database and return the time closest to the current time of day, please?

I set it up so that I have a simple web form that enters the user input of whatever column the user wants. I want the TIME value to be closer to the current time from the selected 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 a user enters "3" into a web form and the current time is 12:10, I would like to return a value of 12:35.

I've been Googling and I believe I need to use CURTIME (). I don't want to be in charge of how to take user input and use it to query that column. I just need to know how to return the column time value closest to the current time please! Thank.

+3


source to share


3 answers


Try:

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

EDIT:



Maybe this is the way

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

"?" is a placeholder for your watchdog time (by PHP).

+3


source


You can do:

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


If you only want the next times that are in the future (for example, when the user has to catch the train), there difference

must be greater than 0, so you would addWHERE TIMEDIFF(column3, CURTIME()) > '00:00'

+1


source


Try it. it selects the last time entry:

SELECT col1 FROM table_name order by col1 desc limit 1;

0


source






More articles:


All Articles