Restaurant opening hours with database table in human readable format using php

Question

I have a table listing restaurant hours. the columns are id, eateries_id, day_of_week, start_time and end_time. Each eatery is presented in the table several times, because for each day there is a separate entry. see this previous question for more information: determine if a restaurant is open (e.g. yelp) using a database, php, js

Now I'm wondering how to take the data from this table and print it in an easy-to-read format. for example, instead of saying "M 1-3, T 1-3, W 1-3, Th 1-3, F 1-8", I would say "M-Th 1-3, F 1-8 ", Similarly, I want" M 1-3, 5-8 "instead of" M 1-3, M 5-8 ". how can i do this without the brute force method of numerous if statements?

thanks.

Answer 1

Thought I would have bash.

Test table

CREATE TABLE `opening_hours` ( `id` int(11) NOT NULL AUTO_INCREMENT, `eateries_id` int(11) DEFAULT NULL, `day_of_week` int(11) DEFAULT NULL, `start_time` time DEFAULT NULL, `end_time` time DEFAULT NULL, PRIMARY KEY (`id`) ) 

Test Data

 INSERT INTO `test`.`opening_hours` ( `eateries_id`, `day_of_week`, `start_time`, `end_time`) SELECT 2 AS eateries_id, 1 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all SELECT 2 AS eateries_id, 1 AS day_of_week, '17:00' AS start_time, '20:00' as end_time union all SELECT 2 AS eateries_id, 2 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all SELECT 2 AS eateries_id, 2 AS day_of_week, '17:00' AS start_time, '20:00' as end_time union all SELECT 2 AS eateries_id, 3 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all SELECT 2 AS eateries_id, 4 AS day_of_week, '13:00' AS start_time, '20:00' as end_time union all SELECT 2 AS eateries_id, 5 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all SELECT 2 AS eateries_id, 6 AS day_of_week, '13:00' AS start_time, '20:00' as end_time union all SELECT 2 AS eateries_id, 7 AS day_of_week, '13:00' AS start_time, '21:00' as end_time union all SELECT 3 AS eateries_id, 1 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all SELECT 3 AS eateries_id, 2 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all SELECT 3 AS eateries_id, 3 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all SELECT 3 AS eateries_id, 4 AS day_of_week, '13:00' AS start_time, '20:00' as end_time union all SELECT 3 AS eateries_id, 5 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all SELECT 3 AS eateries_id, 6 AS day_of_week, '13:00' AS start_time, '20:00' as end_time union all SELECT 3 AS eateries_id, 7 AS day_of_week, '13:00' AS start_time, '21:00' as end_time 

View definition for daylight hours consolidation

 CREATE VIEW `test`.`groupedhours` AS select `test`.`opening_hours`.`eateries_id` AS `eateries_id`, `test`.`opening_hours`.`day_of_week` AS `day_of_week`, group_concat(concat(date_format(`test`.`opening_hours`.`start_time`,'%l'),' - ',date_format(`test`.`opening_hours`.`end_time`,'%l %p')) order by `test`.`opening_hours`.`start_time` ASC separator ', ') AS `OpeningHours` from `test`.`opening_hours` group by `test`.`opening_hours`.`eateries_id`,`test`.`opening_hours`.`day_of_week` 

A request to search for “islands” of adjacent days with the same hours of operation (based on Itzik Ben Gan)

 SET @rownum = NULL; SET @rownum2 = NULL; SELECT S.eateries_id, concat(CASE WHEN S.day_of_week <> E.day_of_week THEN CONCAT(CASE S.day_of_week WHEN 1 THEN 'Su' WHEN 2 THEN 'Mo' WHEN 3 THEN 'Tu' WHEN 4 THEN 'We' WHEN 5 THEN 'Th' WHEN 6 THEN 'Fr' WHEN 7 THEN 'Sa' End, ' - ') ELSE '' END, CASE E.day_of_week WHEN 1 THEN 'Su' WHEN 2 THEN 'Mo' WHEN 3 THEN 'Tu' WHEN 4 THEN 'We' WHEN 5 THEN 'Th' WHEN 6 THEN 'Fr' WHEN 7 THEN 'Sa' End, ' ', S.OpeningHours) AS `Range` FROM ( SELECT A.day_of_week, @rownum := IFNULL(@rownum, 0) + 1 AS rownum, A.eateries_id, A.OpeningHours FROM `test`.`groupedhours` as A WHERE NOT EXISTS(SELECT * FROM `test`.`groupedhours` B WHERE A.eateries_id = B.eateries_id AND A.OpeningHours = B.OpeningHours AND B.day_of_week = A.day_of_week -1) ORDER BY eateries_id,day_of_week) AS S JOIN ( SELECT A.day_of_week, @rownum2 := IFNULL(@rownum2, 0) + 1 AS rownum, A.eateries_id, A.OpeningHours FROM `test`.`groupedhours` as A WHERE NOT EXISTS(SELECT * FROM `test`.`groupedhours` B WHERE A.eateries_id = B.eateries_id AND A.OpeningHours = B.OpeningHours AND B.day_of_week = A.day_of_week + 1) ORDER BY eateries_id,day_of_week) AS E ON S.eateries_id = E.eateries_id AND S.OpeningHours = S.OpeningHours AND S.rownum = E.rownum 

results

 eateries_id Range 2 Su - Mo 1 - 3 PM, 5 - 8 PM 2 Tu 1 - 3 PM 2 We 1 - 8 PM 2 Th 1 - 3 PM 2 Fr 1 - 8 PM 2 Sa 1 - 9 PM 3 Su - Tu 1 - 3 PM 3 We 1 - 8 PM 3 Th 1 - 3 PM 3 Fr 1 - 8 PM 3 Sa 1 - 9 PM 

Answer 2

You want to combine a bunch of intervals for each day. Stick to the 24th format (actually convert it in seconds, I think) until you have to convert it to a human-friendly format.

http://pyinterval.googlecode.com/svn/trunk/html/index.html

The problem is that when you allow seconds ... a restaurant that closes 1 second earlier will be skipped :( Perhaps you need to allow 15 or 5 minute increments. Break the data in the database if you need to. So, the approach: using a temporary data structure, combining all intervals for a given day together. Now change the dictionary. Instead of matching days with intervals, go to intervals within days. Now find a way to intelligently represent these groups of days., set(1,2,3) may be displayed as "MW", so I would claim edlozhil: for each set of the power set {1,2,3,4,5,6,7} (or {1,2,3,4,5} ) to find the best representation of the human (hand) is now firmly encode that logic -. save it in a dictionary that displays the sorted string (this is important), such as “1235” for a human representation, such as “MW, F.” Displaying 1-3, 5-8 is easy as soon as you work with an interval object as described in the link above. Good luck! Let me know what problems you encountered.

EDIT:

This is not the best example that they have (does not show the union of overlapping intervals), but you care about "|" Operator

 unioned: >>> interval[1, 4] | interval[2, 5] interval([1.0, 5.0]) >>> interval[1, 2] | interval[4, 5] interval([1.0, 2.0], [4.0, 5.0]) 

You can simply implement this class yourself, but it may be error prone.