How to get all the data using a time value in MySQL?-CodePudding

mealtime_id mealtime_name   start_time  end_time
1   Breakfast   07:00:00    10:00:00    
2   Lunch   12:00:00    14:30:00    
3   Dinner  18:00:00    20:00:00

In the image above (or the snippet), I have columns start_time and end_time.

How to only show if the current time is 12:03:00 ?

This what I had in mind:

SELECT * FROM ti_mealtimes
WHERE start_time >= '12:03:00' AND end_time <= '12:03:00';

The expected result should be Lunch. Since 12:03:00 is in between 12:00:00 to 14:30:00

mealtime_id mealtime_name   start_time  end_time
2   Lunch   12:00:00    14:30:00

CodePudding user response：

I guess you want:

SELECT * FROM ti_mealtimes
WHERE current_time between start_time and end_time

Or, perhaps, but probably not:

SELECT * FROM ti_mealtimes
WHERE '12:03:00' between start_time and end_time

CodePudding user response：

Your WHERE statement is conflicted. You are telling it to pull records where start_time is at least 12:03:00 and pull records where end_time is no older than 12:03:00

Have you tried BETWEEN?

SELECT
     [mealtime_name]
FROM
     [tbl_name] 
WHERE
     [time] BETWEEN [start_time] AND [end_time]

This should pull the row where [time] is between the two column limits, inclusively.