0

I have below data in my database table

id       time      time_slots
1        time1     09:00-10:30,12:15-14:00,16:30-19:30
2        time2     14:00-15:15,18:30-19:30
3        time3     08:30-10:00,13:00-15:00,15:30-18:00
4        time4     13:00-15:00
5        time5     15:00-16:00,17:30-18:30

Now, if the current time is 13:30 then I want 3 records(id 1,3,4) from the above table. The final result will be:

id
1
3
4

Please help me to find out the solution in the best way. Your help will be very helpful to me. Thanks in advance.

Kushal Nanavati
  • 31
  • 6

1 Answers1

6

I think you can optimize your table structure, else it will take a lot of time to get data when the number of records in the table is large.

try implementing the below structure it will be easy.

Time_table

id       time      
1        time1    
2        time2     
3        time3     
4        time4     
5        time5

Time_slot_table

id   time_table_id(fk)  start_time  end_time
1    1                   09::00      10:30
2    1                   12:15       14:00
3    1                   16:30       19:30
4    2                   14:15       15:15
5    2                   18:30       19:30

Now run below query to get data that comes in range

SELECT time 
FROM Time_table 
JOIN Time_slot_table ON Time_slot_table.time_table_id = Time_table.id 
WHERE current_time BETWEEN Time_slot_table.start_time AND Time_slot_table.end_time
Shobi
  • 10,374
  • 6
  • 46
  • 82
Jigar
  • 3,055
  • 1
  • 32
  • 51
  • @KushalNanavati I haven't verified query let me know if you face any issue in query – Jigar Jun 11 '19 at 06:10
  • Yeah, I have verified and it's working. But I am working on if the case in which I have given in my question (single table with multiple range values). If you find the solution for that then it will be appreciated. – Kushal Nanavati Jun 11 '19 at 06:38