Modelling restaurant opening/closing times and dates

[Mircea Chirea]

How would I store the opening/closing times for a restaurant, as well as the days it is closed?

Let's take a step back. A restaurant has different opening/closing times for each day of the week. So on Monday it might open at 10, on Tuesday at 8 but on Sunday it closes early at 16.

It also might be closed on special days, like on holidays or whatever the reason. These should be specified in advance.

Then I need to get a list of all restaurants and find out which is closed and which is open. For those closed I also need to show the next day it will be open and the interval.

This is both a database design and business logic question. The open/closed filtering can be done client side, as can figuring out the next day. These depend on how the info is stored in the database.

Oh yeah and the next open time has to be calculated as a Datetime, for localized formatting as well as timezone conversion.

[Mircea Chirea]

Actually all time info will be stored as local time (this is all for a single timezone anyway), due to daylight time saving issues.

[Kijana Woodard]

I would assume the time would always be presented in the frame of the restaurant, correct. Knowing that its open midnight to 2pm my time is kind of useless.

Interesting topic. Thanks for starting this thread.

[Kijana Woodard]

Oh, but of course 'is it open now' depends on utc.

When you say client side, one thought I have is that it can be calculated on the app server as opposed to being stored in the db.

I realized recently that my mind had started to try and fit everything in either the db or in js/whatever client code the ui runs leaving the server as a dumb pass through layer. Why? I have no answer for myself. That might alleviate some map reduce complexity right there. ;-)

[Matt Johnson]

Welcome to my world.  :)  And good use case for why UTC is not always the answer.

 

Each restaurant should have a time zone associated with its location.  All times for that restaurant should be stored as local time.

 

For the specific days they are closed or have special hours, use a DateTime.  A DateTimeOffset would also work, but it's not required.

 

For the weekly schedule, use an array of seven days associate with each weekday.  You can use the DayOfWeek enum if you like.  Each element can have a pair of open/close times, as TimeSpan values. You can think of the TimeSpan like the time without the date, but really it represents duration, so when you add it to midnight of a DateTime, you'll get the full date and time of a particular day.

 

You might want to expand the schedule to support multiple times on each day.  Perhaps IDictionary<DayOfWeek,IList<Tuple<TimeSpan,TimeSpan>>> if you want it all in one property.  Or use your own classes.

 

When asking "is the restaurant open now" - you can use TimeZoneInfo to get the Now in the timezone of the restaurant, and compare against your list.

 

When asking "is the restaurant open at 8:00 tomorrow" - make sure you know the timezone of the person who is asking.  Get their time normalized to utc, then convert it to the restaurant's local time before querying.

 

As far as database design goes, I would just have the restaurant's schedule and list of exception dates as properties of the Restaurant entity, stored in the same document. There's not much benefit here to splitting it out into one or more separate docs IMHO.

 

I think you will be able to express everything your asking for in simple range queries.  Stick with an inclusive/exclusive range here.  [OpeningTime,ClosingTime)  or in other words, OpeningTime <= timeToCheck && ClosingTime > timeToCheck

 

You should also have a strategy for dealing with DST if your restaurants could be open late or 24/7.  You still want local time, but on fall-back transitions, do you close before or after the DST change?  A simple bool setting or fixed value would cover it.

[Mircea Chirea]

Alright. First of all, all time data will be stored in UTC+2/+3 (depending on DST) - so I can't really use UTC unless I want to complicate everything for no good reason, as I only have a single timezone to worry about. I need to answer the question "is it open now", as well as the question "if not, when will it be" (well, for the next week anyway; we assume a restaurant won't be closed for a full week).

Client-side means on the server. You can't really do date computations on the database; I'm not sure if Lucene can even do that, but it doesn't matter as it all depends on DateTime.Now. Besides I don't need to filter based on "is open now", I need to split the results, to show the open and closed ones separately. This is fine on the server really.

+ inline

On Saturday, January 5, 2013 9:05:33 AM UTC+2, Matt Johnson wrote:

Welcome to my world.  :)  And good use case for why UTC is not always the answer.

It wouldn't be too bad if it weren't for DST! Oh well, human time sucks. 

 

Each restaurant should have a time zone associated with its location.  All times for that restaurant should be stored as local time.

It's all one time zone, UTC+2 (w/ DST taken into account). 

 

For the specific days they are closed or have special hours, use a DateTime.  A DateTimeOffset would also work, but it's not required.

Right. That would translate to two lists, one for closed with just DateTime, another for special hours with DateTime and the start/end times.

 

For the weekly schedule, use an array of seven days associate with each weekday.  You can use the DayOfWeek enum if you like.  Each element can have a pair of open/close times, as TimeSpan values. You can think of the TimeSpan like the time without the date, but really it represents duration, so when you add it to midnight of a DateTime, you'll get the full date and time of a particular day.

That's what I thought too, it's what I'm currently using. Of course, I hit a wall when I wanted to store DateTime for the close dates; if it's UTC, how do I "convert" the DateTime to the local time? Heck, the time specified there depends on DST, too!

 

You might want to expand the schedule to support multiple times on each day.  Perhaps IDictionary<DayOfWeek,IList<Tuple<TimeSpan,TimeSpan>>> if you want it all in one property.  Or use your own classes.

 

That wouldn't be necessary, at least over here.

When asking "is the restaurant open at 8:00 tomorrow" - make sure you know the timezone of the person who is asking.  Get their time normalized to utc, then convert it to the restaurant's local time before querying.

The question is "when is it open", not "is it open tomorrow at 8"? Not exactly sure how to traverse the week array to leap past the end of the week.

 

As far as database design goes, I would just have the restaurant's schedule and list of exception dates as properties of the Restaurant entity, stored in the same document. There's not much benefit here to splitting it out into one or more separate docs IMHO.

 

Splitting would be like hard-code normalization in SQL. Great, but useless.

I think you will be able to express everything your asking for in simple range queries.  Stick with an inclusive/exclusive range here.  [OpeningTime,ClosingTime)  or in other words, OpeningTime <= timeToCheck && ClosingTime > timeToCheck

 

You should also have a strategy for dealing with DST if your restaurants could be open late or 24/7.  You still want local time, but on fall-back transitions, do you close before or after the DST change?  A simple bool setting or fixed value would cover it.

 

I'll place the burden on the restaurants to specify the closing time correctly, if they closed near the DST transition.

[Matt Johnson]

inline

On Jan 5, 2:10 am, Mircea Chirea <chirea.mir...@gmail.com> wrote:
> Alright. First of all, all time data will be stored in UTC+2/+3 (depending
> on DST) - so I can't really use UTC unless I want to complicate everything
> for no good reason, as I only have a single timezone to worry about.

That's fine that there's just a single timezone. But my advice would
be to put the timezone id as a property on the restaurant instead of
hardcoding it. You never know when in the future you might take your
code elsewhere. Use the timezone id for this, not the offset - i.e.,
the string from TimeZoneInfo.Local.Id.

>I need
> to answer the question "is it open now", as well as the question "if not,
> when will it be" (well, for the next week anyway; we assume a restaurant
> won't be closed for a full week).
>

These should both be easy enough in linq-to-objects with any structure
you can come up with. The harder question would be "give me ALL
restaurants that are open now" as a query against a raven index. It's
still quite doable. Hard to give you code examples until you lock
down the structure of the data.

> Client-side means on the server. You can't really do date computations on
> the database; I'm not sure if Lucene can even do that, but it doesn't
> matter as it all depends on DateTime.Now. Besides I don't need to filter
> based on "is open now", I need to split the results, to show the open and
> closed ones separately. This is fine on the server really.

You actually *can* do date computations and timezone conversions on
the database. You just do them while indexing in Raven. Lucene just
works with the output.

> > For the specific days they are closed or have special hours, use a
> > DateTime.  A DateTimeOffset would also work, but it's not required.
>
> Right. That would translate to two lists, one for closed with just
> DateTime, another for special hours with DateTime and the start/end times.

I would try to compact this to a single list of overrides to the
recurring schedule. Maybe IList<Tuple<DateTime,DateTime,bool>> or the
equivalent. If you are closed for a whole day, then it's just another
range of special hours, but with the bool set to false instead of
true. The math will be easier than having to check two lists.

>
> > For the weekly schedule, use an array of seven days associate with each
> > weekday.  You can use the DayOfWeek enum if you like.  Each element can
> > have a pair of open/close times, as TimeSpan values. You can think of
> > the TimeSpan like the time without the date, but really it represents
> > duration, so when you add it to midnight of a DateTime, you'll get the full
> > date and time of a particular day.
>
> That's what I thought too, it's what I'm currently using. Of course, I hit
> a wall when I wanted to store DateTime for the close dates; if it's UTC,
> how do I "convert" the DateTime to the local time? Heck, the time specified
> there depends on DST, too!

TimeZoneInfo.ConvertTimeBySystemTimeZoneId(dateTime, "UTC",
restaurantTimeZone)

or one of the many other conversion methods. get to know TimeZoneInfo
well if you are going to use windows timezones.

> The question is "when is it open", not "is it open tomorrow at 8"? Not
> exactly sure how to traverse the week array to leap past the end of the
> week.

Use it as a dictionary lookup against the DayOfWeek that you are
comparing. If today is Tuesday, and I want to know if the restaurant
is open, I look at Tuesday's hours.

This only works if you allow for multiple schedules on the same day
and force a cutoff at midnight. So if the restaurant is open from
11AM Friday to 2AM Saturday, store 11:00-24:00 for Friday and store
00:00-02:00 for Saturday. Since Saturday probably has a schedule
also, that's why you need to allow multiple on the same day.

The alternative would be to store 11:00-26:00 on Friday - but then you
may find it tricky to pick which day of week to evaluate.

> I'll place the burden on the restaurants to specify the closing time
> correctly, if they closed near the DST transition.

Sure, but how? Since you're storing as local time, when the clocks
roll back and the restaurant says "we close at 2:00 AM" but there are
TWO moments that represent that time. If you were using specific
dates, I would say to store a DateTimeOffset, but since you are
storing a TimeSpan, then it's probably going to pick the first one if
you do nothing. Maybe this is fine for your restaurants, maybe not.
Maybe your list of special hours and closing dates should be using
DateTimeOffset instead. The times can still be represented as local
time, but then the offset is explicit. So on DST days, you would have
them put in a special entry for that day if they wanted to stay open
until the second 2:00 AM.

May be totally inappropriate, but this reminds me of this anecdote:
http://darwinawards.com/darwin/darwin1999-38.html

[Mircea Chirea]

inline

On Saturday, January 5, 2013 8:06:19 PM UTC+2, Matt Johnson wrote:

inline

On Jan 5, 2:10 am, Mircea Chirea <chirea.mir...@gmail.com> wrote:
> Alright. First of all, all time data will be stored in UTC+2/+3 (depending
> on DST) - so I can't really use UTC unless I want to complicate everything
> for no good reason, as I only have a single timezone to worry about.

That's fine that there's just a single timezone.  But my advice would
be to put the timezone id as a property on the restaurant instead of
hardcoding it.  You never know when in the future you might take your
code elsewhere.  Use the timezone id for this, not the offset - i.e.,
the string from TimeZoneInfo.Local.Id. 

That complicates things for no good reason at the moment. Even if we expand into neighboring countries, they're still in the GMT+2 timezone. If we'd need to specify timezone, I can easily fire up the patch command in studio and add a timezone for all dates.

 

>I need
> to answer the question "is it open now", as well as the question "if not,
> when will it be" (well, for the next week anyway; we assume a restaurant
> won't be closed for a full week).
>

These should both be easy enough in linq-to-objects with any structure
you can come up with.  The harder question would be "give me ALL
restaurants that are open now" as a query against a raven index.  It's
still quite doable.  Hard to give you code examples until you lock
down the structure of the data.

I don't need to ask that question in a query though, I can ask it client side, since I need the list of all regardless if they're open or closed. So it's all fine.

 

> Client-side means on the server. You can't really do date computations on
> the database; I'm not sure if Lucene can even do that, but it doesn't
> matter as it all depends on DateTime.Now. Besides I don't need to filter
> based on "is open now", I need to split the results, to show the open and
> closed ones separately. This is fine on the server really.

You actually *can* do date computations and timezone conversions on
the database.  You just do them while indexing in Raven.  Lucene just
works with the output.

Ahh I see. I can't query on date computations, right? Like q => q.DateProp.Date >= someDate.

 

> > For the specific days they are closed or have special hours, use a
> > DateTime.  A DateTimeOffset would also work, but it's not required.
>
> Right. That would translate to two lists, one for closed with just
> DateTime, another for special hours with DateTime and the start/end times.

I would try to compact this to a single list of overrides to the
recurring schedule. Maybe IList<Tuple<DateTime,DateTime,bool>> or the
equivalent.  If you are closed for a whole day, then it's just another
range of special hours, but with the bool set to false instead of
true.  The math will be easier than having to check two lists.

I'd leave them separated. It's not that big of a deal, it's clearer which is which. 

 

>
> > For the weekly schedule, use an array of seven days associate with each
> > weekday.  You can use the DayOfWeek enum if you like.  Each element can
> > have a pair of open/close times, as TimeSpan values. You can think of
> > the TimeSpan like the time without the date, but really it represents
> > duration, so when you add it to midnight of a DateTime, you'll get the full
> > date and time of a particular day.
>
> That's what I thought too, it's what I'm currently using. Of course, I hit
> a wall when I wanted to store DateTime for the close dates; if it's UTC,
> how do I "convert" the DateTime to the local time? Heck, the time specified
> there depends on DST, too!

TimeZoneInfo.ConvertTimeBySystemTimeZoneId(dateTime, "UTC",
restaurantTimeZone)

or one of the many other conversion methods.  get to know TimeZoneInfo
well if you are going to use windows timezones.

Yeah, I found that class. Could've been made easier to find though, by having time zone conversion into DateTime itself, but what are you gonna do. 

 

> The question is "when is it open", not "is it open tomorrow at 8"? Not
> exactly sure how to traverse the week array to leap past the end of the
> week.

Use it as a dictionary lookup against the DayOfWeek that you are
comparing.  If today is Tuesday, and I want to know if the restaurant
is open, I look at Tuesday's hours.

This only works if you allow for multiple schedules on the same day
and force a cutoff at midnight.  So if the restaurant is open from
11AM Friday to 2AM Saturday, store 11:00-24:00 for Friday and store
00:00-02:00 for Saturday.  Since Saturday probably has a schedule
also, that's why you need to allow multiple on the same day.

The alternative would be to store 11:00-26:00 on Friday - but then you
may find it tricky to pick which day of week to evaluate.

I could also store a boolean saying whether the time crosses into the next day. 

[Mircea Chirea]

Actually storing a boolean is not needed. I can use this condition:

if (hours.Start <= hours.End)

{

    return time >= hours.Start && time < hours.End;

}

else

{

    // The opening time pass into the next day.

    return !(time >= hours.End && time < hours.Start);

}

And when creating an exact DateTime, if the time passes into the next day, I just use .AddDays(1).Date.Add(hours.End). Good point on DST though. I think I'll leave that at default, which picks the first time when say 02:00 happens. I don't know, if I take that into account I complicate things worse.

[Matt Johnson]

That will work as long as you honor that convention everywhere.  It may get in the way for regular range queries though. Maybe in your index you can apply the convention so your end dates are stored that way, but indexed with the +1 already applied.  This is similar to how raven indexes DateTimeOffset as a UTC DateTime.

Also be careful about how TimeSpan gets put in your index.  I believe the default ToString will put anything over 24 hours as whole days.  Instead of 25:00 you will get 1.01:00 which won't sort lexographically in lucene.  Maybe we can do something about that...

---

From: Mircea Chirea
Sent: ‎1/‎5/‎2013 5:45 PM
To: rav...@googlegroups.com
Subject: [RavenDB] Re: Modelling restaurant opening/closing times and dates

[Mircea Chirea]

I don't store anything over 24 hours in TimeSpan. If the end is past midnight, I store end - 24.

[Mircea Chirea]

Now, how the heck do I determine whether a restaurant is open NOW in an index?

TimeInterval[] OpenHours // Start & End as TimeSpan

List<DateTime> ClosedDays

List<Tuple<DateTime, TimeInterval>> SpecialHours

Since I can't depend on DateTime.Now in the index, I need an alternative way to query.