SQL for Opening Hours

Question

In my shops database I need to have the opening hours. Do you have an idea how i can implement this in my dB?

The opening hours are from Monday to Sunday, each day can have 2 opening windows (ex 09:00-12:00, 16:00-19:00)

Answer 1

build another table, call it schedules, add a foreign key to the shops table primary key, a Day of week field, time_open, time_closed. The data should look something like this:

shop_id     day_of_week      time_open        time_closed
1           1                09:00            12:00
1           1                16:00            19:00
1           2                09:00            12:00
1           2                16:00            19:00
1           3                09:00            12:00
1           3                16:00            19:00
1           6                10:00            14:00
2           1                09:00            12:00
2           1                13:00            18:00

This will give you the opportunity to build any kind of schedules, with as many windows as you want, with how many exceptions you need. It's universal, limited only to the fact that it expects all weeks to be identical. No holidays considered, nor odd/even-week schedules that someone might use.

Edit:
With Julien's question, about working hours of a night business, it has come to my attention that the previous solution is not the best bu far. You can't have a bar open at 20:00, close at 06:00, and compare if current time (02:45) is inside this interval, because it won't be. That's why, it would be most convenient to register not the closing time, but the total working time, in the convenient unit of measure (minutes for example).

shop_id     day_of_week      time_open        working_time
1           1                09:00            180
1           1                16:00            180
1           2                09:00            180
1           2                16:00            180
1           3                09:00            180
1           3                16:00            180
1           6                10:00            240
2           1                09:00            180
2           1                13:00            300