How to determine the nearest, previous business day for an event given custom business hours?

Question

For a given Business with custom BusinessHours and Events, I need to figure out how get the nearest, previous business day for each event (say a weeks worth of events). For example, let's say a Business has BusinessHours on Sunday, Wednesday, Friday, and Saturday. Given an Event that starts on Wed, June 22nd, 2011 at 3pm, how can I efficiently determine that Sunday, June 19th, 2011 is the nearest, previous business day to this event? Here are the models:

class Business(models.Model):
  name = models.CharField(max_length=50)

class BusinessHours(models.Model):
  """
  I realize there are better ways to store business hours, 
  but this approach is simple and serves my purposes for now.
  However, if another schema solves the problem above more efficiently, 
  feel free to make a suggestion.
  """
  business = models.ForeignKey(Business)
  sunday_open = models.TimeField(blank=True, null=True)
  sunday_close = models.TimeField(blank=True, null=True)
  monday_open = models.TimeField(blank=True, null=True)
  monday_close = models.TimeField(blank=True, null=True)
  ... continue for each day ...

class Event(models.Model):
  business = models.ForeignKey(Business)
  start = models.DateTimeField()
  end = models.DateTimeField()

I'm assuming most of the work needs to happen in python apart from Django, so ignore the Django models if it complicates the solution. I'd be happy to provide additional info if needed. Thanks in advance!

score 3 · Accepted Answer · edited May 23 '17 at 11:55

You will want to do a query to your database written in python. I would check out the django docs on how to make database queries and the appendix of fieldlookups.

The basic format will probably look something like :

# Will return a list of dictionary objects for all rows with that foreign key
# Ex: [{'business' : '3', 'monday_open' : someTime, 'monday_close' : someTime...},...]
storeHours = BuisnessHours.objects.values().filter(business = *foreign key*)

# You can also get your even like this
# Ex: [{'business' : '3', 'start' : someTime, 'end' : someTime}, {'business' : '3'...]
storeEvent = Event.objects.values().filter(business = *same foreign key as above*)

*Note, if you have different events you would like to save per store, it might be good to have a 'name' column in the Event model so you can also query based off of a certain event. Also, instead of doing a TimeField, try a DateTimeField that way you can save the date too if you want.

After you get your query dictionaries back, it should be simple to group the start and end times in python, seeing which ones fall closest to the range of the event. For this, I would also take a look at the datetime module.

I would also have a look at this question. He does some pretty interesting things with list comprehension in the query format.

There is probably a more efficient way to do this simply with fieldlookups though so I would look into that too.

Thank you for your answer! Your suggestion to use values() helped point me in the right direction. I'm currently using values_list() to produce a list of open times for each day: [sunday_open, monday_open, tuesday_open, wednesday_open, thursday_open, friday_open, saturday_open]. In this way, I can use the .weekday() of the current event as the index to start walking through the list until I find the previous day when the business is open. I'm still working through the details, but I hope to provide more details in the next day or two. — Michael Godshall, Jun 16 '11 at 22:26
I'm glad you found it helpful! Looks like you are on a pretty good path to figuring it out : ) I look forward to hearing more about it — mshell_lauren, Jun 16 '11 at 23:28

How to determine the nearest, previous business day for an event given custom business hours?

1 Answers1