mySQL : using BETWEEN in table ?

Question

I have a table that includes some student group name, lesson time, day names like Schedule. I am using C# with MySql and I want to find which lesson is when user press button from table.

I can find it by entering the exact value like in the table, e.g. 08:30 or 10:25. But I want to make that getting system time and checking that it is between 08:30 and 10:25 or 10:25 and 12:30. Then I can say that it is the first or second lesson.

I have also table includes Table_Time column has 5 record like 08:20 , 10:25 , 12:20 so on. Could I use like :

select Lesson_Time 
from mydb.clock 
where Lesson_Time between (current time)-30 AND (current time)+30  

Or can I use between operator between two columns (like creating Lesson_Time_Start and Lesson_Time_End) and comparing current time like Lesson_Start_Time < current time < Lesson_End_Time?

EDIT: More precisely ,problem is how can I check current time is which record

I have table like

Time_ID  | Lesson_Time 
    1       08:30
    2       10:25
    3       12:20
    4       14:15
    5       16:10

And When I press some button I want to check that current time when I pressed button is it equel to which Time_ID Forexample I pressed button at 09:15 and it must select 1th record , if I pressed button at 12:00 it must select 2th record.

Answer 1

I'm not entirely sure what you want to do, but I think you are looking for DATE_ADD and DATE_SUB:

SELECT Lesson_Time from mydb.clock
 WHERE Lesson_Time between DATE_SUB(NOW(), INTERVAL 30 MINUTE) AND 
                           DATE_ADD(NOW(), INTERVAL 30 MINUTE);

provided Lesson_Time is a proper DATETIME field, this should work.

Answer 2

I'd try something like this.

select
    Time_ID,
    Lesson_Time
from
    mydb.clock
where
    Lesson_Time <= TIME(NOW())
order by
    Lesson_Time DESC
limit 0, 1

The query is limited to lesson time records prior to the current time sorted descending by time and returns only the first record, which should be the record you want. (Note, however, that midnight can complicate matters.)