Represent time interval in mysql Database

How can represent a time in interval in mysql database in a way that I can index them later .

For example:

--------------------------------------------------------
   id  |       name      |      time_available      |
   --------------------------------------------------
   1   |       John      |     From 2 AM to 3 AM    |
   --------------------------------------------------
   2   |       Edward    |     From 11 PM to 3 AM   |
   --------------------------------------------------
   3   |       Jacob     |     From 1 PM  to 4 PM   |
----------------------------------------------------------

I want to select all the the names and the id of the users where they are available in 2:30 AM . I have no clue how to do it.

If you want to store availability in a table, you should separate the start time and end time of shift in 2 columns.

Your table would then be:
create table support_staff_schedule
( id int not null,
name varchar(50),
time_start int,
time_end int)

alter table support_staff_schedule add index time_start_end (time_start desc,time_end);

The time is set as integer because it doesn't look like you care for the date. If date is included then you would have to create a new row for every schedule for a given person. I'm assuming your business rule is: "Same schedule every day unless modified"

I use time_start in descending order because technically you should be spending most of your time looking at the recent schedule rather than past schedule. If your business requirements are different, you may want to change that.

StackExchange Database Administrators Q#124270, answer score: 2