Opening hours

[NetGeni.us]

hi, I'm in the process of building a PHP-based directory system for my local university listings businesses and their opening hours.

I could just have a text field to display the opening hours, i.e.

Monday: 9-5
Tuesday: 9-5
Wednesday: 9-5
Thursday: 8-5
Friday: 8-7
Saturday: 8-7
Sunday: Closed

But I wanted the page that listed the businesses to be dynamic and show a little icon next to the listing to show if it was open at that time.

The problem I have is that I don't know the best way to store this information in the database to achieve such results.

I figured I could have a table that looked something like this:

monday_open
monday_close
tuesday_open
tuesday_close
....

with each field storing a number between 0 and 23 - but this seems quite inflexible, seeing as some businesses might open at 9:30 rather than 9, and some other businesses might close for lunch.

So, yeah.... can anyone suggest a good system for me to store this information, to achieve the aim above?

Thank you

[Mittineague]

You're on the right track. But the database will need to be more complex.
Instead of using "human" time, use timestamp. That way you can "fine tune" the times to the second.
But you may need to consider multiple times. For example 7-11:30 1-5 (hour and a half lunch break). Or Lobby: 8AM-6PM Drive through: 6AM-8PM Call center: 7AM-10PM

Edit* On second thought, using timestamp may be more work converting back and forth than is needed. You could use "computer time", i.e. 00:00:00-24:59:59 instead so that 7:30AM-5PM would be 073000-170000

[NetGeni.us]

OK, I'm glad to hear I'm going in the right direction with this, but it's looking like it's going to have to be a bit more complex than I had hoped.

Lets say I was going to assume that they only have one set of opening hours per day, you've suggested using 'computer time', what type of field should I use for this, and what format should it be in? (I can't imagine that I'm going to need something so precise as to the second - in fact, I'm pretty sure that I'll be able to get away with on-the-hour, quarter-past, half-past and quarter-to).

Are you suggesting that I use an INT in the database, with a 24h value in it (i.e. 1730 for 5:30pm), then get the current time using date("Hi") and seeing if it's between the two values set?

Alternatively, I had another idea which would solve the multiple opening times problem, a table like this:

day_of_the_week - INT (0 for sunday to 6 for saturday)
opening_hour INT (0000 to 2359)
closing_hour INT (0000 to 2359)

then I can check what day of the week it is, and if the current time is within the hours specified. How does that sound? I think it's going to be a bit harder to administer, seeing as these values are due to be set by the businesses themselves.

[Mittineague]

Getting the current time as 1545 is easy enough eg.

$current_time = date("Hi"); // hhmm 

but depending on how you get the times from the businesses could be a problem. For example if they use a string "three thirty PM", or "3:30 PM", instead of 15:30
Can you restrict the format by getting the values from a form or are you getting them another way?
You may be able to handle "oddities" by having a "Notes" field for things like "closed Noon to One", "Lobby only after 5" etc.

[NetGeni.us]

Oh I'm pretty sure that I'll be able to limit their input method using a drop-down-list, I'll just have three of them, one with the day of the week, and two for the hour/min opening time, and two for the hour/min closing time.

They would enter the info in for, say, Mondays, which after the page has refreshed, will be then displayed as a range of hours they are open, when the option to then add another range for the Tuesdays, etc..

This is seeming like the better way of doing it, but I guess the proof is in the pudding.

[Mittineague]

Having a form will make it a bit more work for them but will simplify your coding immensely. You should be able to do something like

$current_time =  date("Hi"); // hhmm
$open_time = 0800;//from db
$close_time = 1600;//from db
if (($current_time >= $open_time) && ($current_time <= $close_time))
{
//business is open stuff
//.... etc.