CODEWITHSUNDEEP
phpmysql
VanZan
VanZan

Reputation: 65

MYSQL: Return column time value based on current time?

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.

Upvotes: 3

Views: 145

Answers (3)

Ewald
Ewald

Reputation: 46

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'

Upvotes: 1

Per76
Per76

Reputation: 184

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

Upvotes: 3

Abhishek Ginani
Abhishek Ginani

Reputation: 4751

Try this. it selects the last entry of time:

SELECT col1 FROM table_name order by col1 desc limit 1;

Upvotes: 0

Related Questions