CI Learn CI Learn - 4 months ago 9
MySQL Question

Get closest time to current time php

I have table like this:

Database Image

Assume current date and time is

2016-07-14 23:43:00
.

Question is I need to get next time after the current time. But it should be within the day. (
2016-07-14
not
2016-07-15
)


Time will be user input so cant call it with ID.


Example: if I add the code it will gives me
2016-07-14 23:45:00
.

Is there any way to achieve this? I have no idea about this. Please help me.


I use query like this for now.
SELECT * FROM video WHERE datetime > '$now' ORDER BY datetime LIMIT 1
.

Answer

You should just need to calculate the start of the next day, and take values less than it.

SELECT * FROM video 
WHERE datetime > '$now'
   AND datetime < CAST(DATE('$now') + INTERVAL 1 DAY AS DATETIME)
ORDER BY datetime 
LIMIT 1
Comments