Comparing datetime to hours of operation

[mdupras]

I'm trying to figure out how I can compare the current datetime to information in a table about what days of the week and hours a restaurant is open. What I want to do is be able to filter a restaurant database based on what restaurants are open right now. Because the days of the week and hours a restaurant is open can vary from day to day, I assume I have to have that information parsed pretty granularly in the table, with numerous fields representing the opening and closing time for each day of the week.

Any thoughts on how to accomplish something like this in Caspio?

Thanks!

-- Mike

[sdi126]

Well I can say it is definetly possible as I just did it (http://bridge.caspio.net/dp.asp?AppKey=faae0000f3a4h3e4j7e4c3f4e2j4)...it is rather hard to explain but I will try

I used three tables

Table 1:

Holds the days of the Week (Sunday - Saturday)

They are uniquely identified by a number (0-6) 0 is Sunday

Table 2:

Holds the restaurant information and a number field that uniquely identifies the restaurant.

Table 3:

Holds the days and times a restaurant is open.

There is a field for the start time

There is a field for the end time

There is a field for the day of the week (from table 1)

There is a field to tell you what restaurant this is (id from table 2)

A view is created to link all of this information together as if it is all in one table.

The caspio datapage is a search and report (with advanced options and parameters turned on)

Your datasource will be the view you created based on all three tables.

You will be configuring a search form with three hidden fields

one for the start time,one for the end time, and one for the day

You will need to use javascript to dynmically determine the current date and time when the user visits the page and use this information to update the hidden fields and submit the form.

So basically when you have the javascript telling you the current hours you can make your query say greater than or equal to the start time and less than or equal to the end time and the day = day to return the proper restaurants that are currently open...wow I'm sure this makes no sense.

Maybe it will spark further discussions!

[mdupras]

That's an interesting solution, and it does make sense. A couple of questions, though:

-- Why do you need to handle the days of the week as numbers? Does that have something to do with how Java and/or Caspio handles date/time?

-- How do you input the hours into the database? As standard time (8:30 p.m.) or 24-hour time (2030)?

Thanks for the help and reply!

-- Mike

P.S. On clicking your link, hour 19 day 5, I'm getting \"no record found\" ...

[mdupras]

Another question/thought: What if a restaurant's open and close times span across a day? In other words, say they open at 4 pm and close at 2 am? That would seem to complicate the \"greater than-less than\" way to compare the time ...

-- Mike