SELECT restaurants OPEN $now

l008com

I've got this database query I use to select restaurants from a database, which are currently open. "Currently" comes from the browser via javascript, so it gets hard-coded into the query. This query seems to be working great, but I still wanted to run it by more eyes before I move on to the next part of this site.

So what makes this task (selecting currently open restaurants) more complicated than you initially might think, is that many restaurants, particularly on the weekends, are open past midnight. But those few hours past midnight, are still considered part of the day before. I store the times in 24-hour time, and so for times after midnight, I just keep going. For example, if a restaurant closes at 2am, the close time gets stored in my database as 26:00:00.

So my restaurant table rest is very straight forward, the only relevant fields are:

id / name / city

7 / 'Jims' / 36

Hours are stored in a table called hours. Each day that a restaurant is open, has it's own row in hours. So if a restaurant is open monday through friday, there will be 5 entries in hours, for that particular restaurant. As I said above, close times after midnight are entered as times past 24:00:00.

id / rid / day / open / close

63 / 7 / 5 / 12:00:00 / 26:00:00

id is a unique id.
rid is the id of the restaurant.
day is the day of the week.
open and close are the open and close times.

So in the sample row above, it would indicate that restaurant 7 (Jims), is open on Friday from noon till 2am.

Also note that if a restaurant isn't open on a day, there is simply no entry for that day. And if a restaurant's hours are unknown, there are no entries in the hours table for that restaurant at all.

So with all of that info, here is a query I use to select restaurants that are open right now:

SELECT
	`rest`.`id` AS `id`,
	`rest`.`name` AS `name`
FROM
	`rest`
	LEFT JOIN `hours`
		ON `hours`.`rid` = `rest`.`id`
WHERE
	`rest`.`city` = $city
	$filters
	AND ( (`hours`.`day` = $today AND '$now' > `hours`.`open` AND '$now' < `hours`.`close`)
		OR (`hours`.`day` = $yesterday AND '$now' < SUBTIME(`hours`.`close`,'24:00:00')) )
GROUP BY
	`rest`.`id`
ORDER BY
	TRIM(LEADING 'The ' FROM `rest`.`name`)

$today and $yesterday are integers of the appropriate days of the week, which gets figured out in javascript and passed along to php. $now is the current time that gets passed the same way. I get this info in javascript so I don't have to worry about time zones. Everything is always local to the user. Note the odd OR in the WHERE clause, where I have to see if $now is within today's open and close hours, but I also have to see if $now is before $yesterday's close hours, since many hours 'leak' past midnight. $filters are just some other WHERE checks for simple columns in rest (i.e."AND rest.delivers = 1")

So this is my solution. Any problems with my logic? Or my query?

The only other obvious alternative to the way I do it, would be to break up each day's hours into separate pieces. For example, a single restaurant would have to hours entries for Saturday. A midnight to 2am row, and then a noon to midnight row (assuming the restaurant was open till 2am friday night, and then opened noon to 2am saturday night too). Saturday night's midnight to 2am would get entered in as one of sunday's hours. Storing my database like this would make my queries a little simpler but it would make the overall displaying of hours and open & close days much more complicated.

l008com

Here's a link to the live page this code is running on, if you want to see it in action:

http://new.menulizard.com/by-city/

johanafm

PHP's mktime function works with numbers outside the range limits for each parameter (day, hours etc).

l008com

johanafm;10997735 wrote:
PHP's mktime function works with numbers outside the range limits for each parameter (day, hours etc).

Ok
Buuuuut I'm not sure why you're telling me this?

johanafm

Since for example 26 is outside of the range for hours.

l008com

Yeah but is 26 going to cause a problem? It seems to be working fine so far.

johanafm

Like I said, numbers outside the ranges for days (of months), hours etc WORK with php's mktime function. WORK means NO PROBLEM, otherwise I'd have said that they do NOT work, or possibly I wouldn't have mentioned it at all. Just like I didn't mention that numbers outside ranges do NOT work with for example MySql's DATE_FORMAT.

Have you TRIED using mktime with 26 for hours? What results did you get? If the result you got didn't make any sense to you, did you READ the manual page for mktime that I linked to? Did you read the first example which showed how to use it with date to get the results in a format that makes sense to a human?

If so, did you try using that with date to get it in a meaningful format? What results did that give you?

If not, what did you try?

l008com

johanafm;10997834 wrote:
Like I said, numbers outside the ranges for days (of months), hours etc WORK with php's mktime function. WORK means NO PROBLEM, otherwise I'd have said that they do NOT work, or possibly I wouldn't have mentioned it at all. Just like I didn't mention that numbers outside ranges do NOT work with for example MySql's DATE_FORMAT.

Have you TRIED using mktime with 26 for hours? What results did you get? If the result you got didn't make any sense to you, did you READ the manual page for mktime that I linked to? Did you read the first example which showed how to use it with date to get the results in a format that makes sense to a human?

If so, did you try using that with date to get it in a meaningful format? What results did that give you?

If not, what did you try?

No I have not tried using mktime with 26 hours. Why would I? I don't understand why you are even suggesting it. What are you suggesting I use mktime for? I feel like I'm missing something here.

johanafm

# Notice that this is january FIRST, 26:00?
$t = mktime(26, 0, 0, 1, 1, 2012);
echo $t . PHP_EOL;

# What does this say?
$d = date('Y-m-d H:i:s', $t);
echo $d;

Now, can you think of a way to use this information with your database to solve your initial problem? If not, post back again.

l008com

I'm still completely lost as to what your point is.

johanafm

You have opening and closing hours in the db. The user gives a specific time or you use current time as you select closing hour when you check for restaurants which may be open, comparing opening hour < given time.

# unix timestamp
$t = searchTime();

$q = printf("SELECT id, close FROM ... as before... WHERE as before ... but remove check against close");

# query database and return array of rows
$rows = getRowsForQuery($q);

# put array elements of places that actually are open here
$open = array();
$d = date('d');
$m = date('m');
$y = date('y');
foreach ($rows as $r)
{
	if ($t < mktime($r['close'], 0, 0, $m, $d, $y)
	{
		$open[] = $r;
	}
}
# now you have results of restaurants which still have not closed.

However, I should have started out by recommending that you store your data with the proper data types, which in this case is one datetime field for opening hour and one datetime field for closing hour, thus giving you

id	opening			closing
1	2012-03-01 15:00	2012-03-02 02:00

And then you just take the current datetime and

WHERE NOW() BETWEEN opening and closing

l008com

Ok but why would you take the time-check out of the database query and put it into PHP? Seems like the query is the proper place for it.

Also storing the open and closing times as full dates... how would that work? Restaurant's hours are good for every week, not just specific times.

johanafm

You do not store it using the proper data type in the database. The database can't handle times beyond the interval [0-24[ while PHP can. But you could certainly use a CASE statement in your query to handle this directly in the query. But once again, I'd much rather switch to using two DATETIME fields for storage while dropping the "day" field, and do deal with this in the query as I said in my last reply.

The choice is of course yours, as always.

cretaceous

how do you store 24 hour eating places?
maybe they don't exist except at motorways or airports and maybe those are not what you call a restaurant..

I think your pattern seems dandy, though you never know when something might arise to bite your ass in the future

l008com

I haven't had any 24 hour places yet, but I'd just do 00:00 to 24:00, or maybe it was 00:01. I don't know off the top of my head, but I actually just recently found some original code from 2006 to handle just that, and display "All Day" instead of times, for restaurants that were open all day on a particular day. But as I said, I don't have any yet.