Determine if the restaurant is currently open (for example, yelp) using the database, php, js

Question

I was wondering if anyone knows how screech determines which restaurants are "opening now"? I am developing a similar application using html / javascript / php. I was going to have a column in my database for every day, with commas divided by hours recorded in the format “2243” (22:43). for example, if the restaurant is open for lunch and dinner, it could be "1100,1400,1700,2200". then I would check (using js) if the current time falls into one of the ranges of the current day. I would also like to be able to determine if the restaurant is open "tonight", "open late", etc., for those whom I assume I would check if the open range covers certain ranges.

Is there a better way to do this? in particular, how to store the clock in a database, and then determine whether it overlaps with a given set of clocks.

thanks.

Answer 1

You definitely want to reverse engineer the database. Putting multiple values ​​in one column like this is a huge violation of normalization rules and will cause many headaches in the future. One column should always contain separate information.

You must also use the correct data types. Do not put time in a string because you can end up with “foo” as time, and then what do you do?

Instead, you probably want to:

CREATE TABLE Restaurants ( restaurant_id INT NOT NULL, restaurant_name VARCHAR(40) NOT NULL, CONSTRAINT PK_Restaurants PRIMARY KEY CLUSTERED (restaurant_id) ) CREATE TABLE Restaurant_Hours ( restaurant_id INT NOT NULL, hours_id INT NOT NULL, day_of_week SMALLINT NOT NULL, start_time TIME NOT NULL, -- Depends on your RDBMS and which date/time datatypes it supports end_time TIME NOT NULL, CONSTRAINT PK_Restaurant_Hours PRIMARY KEY CLUSTERED (restaurant_id, hours_id) ) 

Then you can easily check open restaurants at specific times:

 SELECT R.restaurant_id, R.restaurant_name FROM Restaurants R WHERE EXISTS ( SELECT * FROM Restaurant_Hours RH WHERE RH.restaurant_id = R.restaurant_id AND RH.start_time <= @time AND RH.end_time >= @time AND RH.day_of_week = @day_of_week ) 

If you have a time interval that spans midnight, you will need to have two rows — one on the first day and one at midnight — “x” the next day. Also, be sure to consider time zones when using the GUI.

Answer 2

I would have a table named REST_HOURS with the following fields:

 CREATE TABLE REST_HOURS ( REST_ID integer NOT NULL ,OPEN_TIME time NOT NULL ,CLOSE_TIME time NOT NULL PRIMARY KEY ( `REST_ID` ) ) ; 

on OPEN_TIME and CLOSE_TIME

I would make a request like:

 select REST_ID from REST_HOURS where CURTIME() >= OPEN_TIME and CURTIME() <= CLOSE_TIME 

Of course, you can replace CURTIME () at any time (you can add a day of the week for opening and closing, as some restaurants have different hours, for example, Sunday)

Answer 3

If you can use Postgres 9.4, you can use something like this https://github.com/AndrewPashkin/pytempo