Opening Closing TImes in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Opening Closing TImes in SQL

Author

Topic

bateman_ap
Starting Member

5 Posts

Posted - 2002-10-02 : 09:13:26

I am setting up a table detailing opening and closing times for a number of restaurants.

Because they change every day for each establishment I think I should do something like:
mon_lunch_open, mon_lunch_close, mon_dinner_open, mon_dinner_close

What would be the best format to use to populate these fields, should I use the 24 hour clock and if so what should I use to show it is closed? Should the field types be int or numeric or something else? Also there may be a time when dinner_close goes into the early hours of the next morning, how should this be dealt with? I just can't work it out!!!

Many thanks
Charles

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-02 : 09:30:15

I'd suggest a different table structure, something like:

CREATE TABLE HoursOpen (Restaurant varchar(30) NOT NULL,
DayOfWeek varchar(10) NOT NULL,
MealType varchar(10) NOT NULL,
OpenTime smalldatetime NOT NULL,
CloseTime smalldatetime NOT NULL)

You'd then have data like:

INSERT INTO HoursOpen VALUES ('Mom''s Pizzeria', 'All', 'Breakfast', '08:00:00', '11:30:00')
INSERT INTO HoursOpen VALUES ('Mom''s Pizzeria', 'All', 'Lunch', '11:30:00', '17:00:00')
INSERT INTO HoursOpen VALUES ('Mom''s Pizzeria', 'All', 'Dinner', '17:00:00', '23:00:00')
INSERT INTO HoursOpen VALUES ('Tuesday''s 24 Hour Food', 'Tuesday', 'All', '08:00:00', '08:00:00')

Doing it this way is a more normalized structure that will allow you to handle changes far more easily than trying to put an entire week into one row. If someone is not open Tuesday, for example, or is open all day every day, you won't end up with empty columns nor will you have multiple, unnecessary rows.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-02 : 09:31:33

Why not have a datetime field.

Then 2:00 pm would be 1 jan 1900 14:00
If you add this to todays date you will get 2:00 pm today.

1:00 am tomorrow would be
2 jan 1900 1:00 am

It is held as a decimal number. The integer part is the day from 1 jan 1900 and the decimal part the time.

Consider having a table with something like
day, start, end

or even

type, date, precedence, start, end

then you could hold recurring times, type = day of week, weekday, bank holliday, specific date...
precedence shows what happens if to dates coincide - like specific date and week day.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bateman_ap
Starting Member

5 Posts

Posted - 2002-10-02 : 09:33:41

Many thanks, just a couple more questions if I may, most of the restaurants have different hours Tuesday - Friday comapred to Sat and Sun different again, with some closed on Monday so would I do this in seperate rows or do something that says Tues-Thurs

Also what happens in the case of a restaruant for Sat dinner opens until 03:00 sunday morning, do i just put 03:00 sat?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-02 : 09:39:28

You'd have 3 rows for Tuesday, Wednesday, and Thursday, each with their open and close times, even if those times are the same. Basically, each restaurant would have at least 7 rows, one for each weekday, more rows if they are only open for certain meals.

If the place is closed on Mondays, for example, you can do 2 things: either have no row at all for Monday, or create a MealType or something that says "Closed". It might be better to do the latter, because you want to specifically say that the place is closed. If there was no data at all for that day, you'd have to infer that it's closed, and it would be harder to process (and much easier to forget!)

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-10-02 : 09:49:36

why not have a OpenTime OpenLength and then just add what ever part the OpenLength is ... i would suggest minutes ... then

'MONDAY', 'LUNCH', '11:00:00', 120

then just add 120 to the OpenTime and you get the result ... you could take the open time to mean today's date at this time plus this time length of being open ... ya it would look weird but it would be flexible ...

if i missed something don't mind me i'm on crack this monring...

bateman_ap
Starting Member

5 Posts

Posted - 2002-10-02 : 10:17:15

Thanks for all the great replys. Gonna try most of em and see which gets the best results.

Just one last bit, my date and time understandment is rather lacking! I am displaying a smalldatetime value in my asp page and using it the time is shown as 01/01/1900 19:00:00

If I want it to show as "7pm" is this possible? or even 19:00?

r937
Posting Yak Master

112 Posts

Posted - 2002-10-02 : 10:26:51

i gotta put my two cents in here for the denormalized solution of one (weekly) row per restaurant being much simpler than a fully normalized solution (one row per day of week)

with seven slots, or seven pairs of slots, you could use nulls in those time slots for days where the restaurant is closed

otherwise you end up with -- excuse me, no offence intended -- abominations like "create a MealType or something that says "Closed"

the problem of missing rows in the normalized solution is that the logic is complicated by the real-world reality that people are going to want to see "closed" when the database rows aren't there, so you have to "materialize" them, at least insofar as supplying nulls for them, and that takes extra logic, so why not have slots where nulls can be stored

as far as handling the "Tues-Thurs 9am-11pm" recurrence, again, with daily slots you would have to have complex looping logic to detect this, and let's face it, it's more user-friendly to print "Tues-Thurs 9am-11pm" than "Tues 9am-11pm Wed 9am-11pm Thurs 9am-11pm"

all things considered, it sounds to me like multiple rows per restaurant and datetime datatypes is over-engineering the solution

is there going to be any analysis of durations? average number of hours open tuesdays? total number of restaurants actually open at 12:30pm on wednesdays?

i didn't think so

make it a varchar, large enough to hold the largest string, and store the data as strings like the following:

"Mon Closed, Tues-Thurs 9am-11pm, Fri-Sat 9am-1am, Sun Closed"

rudy
http://rudy.ca/

bateman_ap
Starting Member

5 Posts

Posted - 2002-10-02 : 10:32:08

The only thing that there will be is that eventually the user would be able to include a time in a search. ie open monday at 9.30

I think reading everything using nulls in to show when closed seems to be the best idea. A quick bit of code to check and if null prints up CLOSED seems to work a treat.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-02 : 12:23:11

quote:
otherwise you end up with -- excuse me, no offence intended -- abominations like "create a MealType or something that says "Closed"

Yeah, that is kinda Frankenstein-ish, isn't it???

<Gene Wilder voice> GIVE MY CREATION....LIIIIIIIIIIIIFE! </Gene Wilder voice>

quote:
as far as handling the "Tues-Thurs 9am-11pm" recurrence, again, with daily slots you would have to have complex looping logic to detect this, and let's face it, it's more user-friendly to print "Tues-Thurs 9am-11pm" than "Tues 9am-11pm Wed 9am-11pm Thurs 9am-11pm"

Yeah, but if you use Tue-Thu, how do you query for restaurants that are open on Wednesday? You'd need some kind of logic to figure it out from that, and what if the place is open Tue and Thu but NOT Wed? You'd have to have separate rows for each day anyway. Sorry Rudy, using nulls are a great idea, but...

quote:
make it a varchar, large enough to hold the largest string, and store the data as strings like the following: "Mon Closed, Tues-Thurs 9am-11pm, Fri-Sat 9am-1am, Sun Closed"

I can't agree with that one.

r937
Posting Yak Master

112 Posts

Posted - 2002-10-02 : 13:07:49

nor should you, rob

the key questions were "is there going to be any analysis of durations? average number of hours open tuesdays? total number of restaurants actually open at 12:30pm on wednesdays?"

obviously, if there is going to be a feature like finding all restaurants open on day X at time Y, then the days and times have to be stored discretely somehow

my suggestion for a single varchar was based on a simple solution to an information retrieval need, and a desire not to over-engineer a solution if it wasn't necessary

so if search is required, on day and/or time open, then yeah, separate buckets for that

whether they are separate rows or denormalized, that's a subsidiary discussion, and if it were me, i might go either way...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources