Format store hours in PHP from MySQL timestamp

[eluser]Unknown[/eluser]
Hi,

I am struggling to find a methodology for formatting the store hours for a restaurant that I am developing a simple CMS for using CI.

Basically, the restaurant owner has a page in his administration section where they can enter their store hours into a form. These hours are stored as timestamps in MySQL. What I want is to format the store hours like they are typically displayed on websites. Here is an example...

Monday-Friday | 7:00am - 10:00pm
Saturday | 7:00am - 2:00am
Sunday | 10:00am - Midnight

I am having the users enter the open and close times for each day of the week individually. This is because I want them to eventually be able to select how they want the hours displayed, and my application would format them for display. Therefore, the database looks like:

mon_open = timestamp
mon_close = timestamp
tue_open = timestamp
tue_close = timestamp
wed_open = timestamp
etc.....

I am struggling to find a way to easily group together days that have the same time. For instance, if monday, tuesday and wednesday all have the same open and close time, the the format should be...

Mon-Wed | open - close

Is there a way to do this without a ton of nested "if" statements? Keep in mind that there may be instances where there ends up being multiple groups of similar hours. Example:

Monday - Wednesday | 7:00a - 12:00a
Thursday - Friday | 8:00a - 2:00a
Saturday | 10:00a - 2:00a
Sunday | 10:00a - 10:00p

Any guidance on how this could be easily accomplished would be great.

Regards,

Kevin

[eluser]kgill[/eluser]
This seems like an overly complicated concept for a simple display. Is there any other use for the timestamps or are you just keeping it in the DB for only displaying the store hours? If its only purpose is to show on the website why not let the end user decide how they want things to show and save yourself tons of programming. Instead of writing a bunch of code to format time to satisfy every way someone would want to display it and lots of logic to determine how things get grouped dumb it down to free form text and a simple select.

Code:

// table
store_hours:
seq|store_id|day_text|hour_text
1|1234|Monday-Wednesay|7:00-12:00
2|1234|Thursday|9:00-5:00
3|1234|Friday-Saturday|10:00-1:00
4|1234|Sunday|Closed
1|9999|Mon-Fri|9am-5pm
2|9999|Sat|11am-5pm

select day_text, hour_text from store_hours where store_id = 1234 order by seq

If there's some actual reason it needs to be a timestamp then to group them then the basic logic would go something like this: loop through each day of the week, tracking current day and previous day. Check if the current day has the same hours as the previous day if it does it doesn't get shown. If it's different you write the previous day out.