One of the many tasks that’s been languishing on my ’to do’ list has been improving the handling of opening/operating/business hours for Grand Rapids WiFi. So today, in a fit of wanting to reduce that ever-present list, I decided to take a proper stab at it. Since it’s got to be a fairly standard situation but I couldn’t find any other write ups online I thought the process might be worth a little documentation.

So far I’d been modelling business hours using the rather naive approach of having a column for the opening and closing times for each day. A snippet of the rails migration to create the table (adjusted to use the DRYed up migrations in Edge Rails, and then expanded for clarity) would therefore be:

create_table :locations do |t|
	t.string :name
	t.time :monday_open, :monday_close
	t.time :tuesday_open, :tuesday_close
	t.time :wednesday_open, :wednesday_close
	t.time :thursday_open, :thursday_close
	t.time :friday_open, :friday_close
	t.time :saturday_open, :saturday_close
	t.time :sunday_open, :sunday_close
end

This approach had several things going for it, primarily that it became easy to use a MySQL BETWEEN() query to check whether a location was open at a given time, and that representing this data in forms was nice and simple. But it quickly fell down when places were open 24 hours, or open post-midnight.

There are a huge number of options for business hours and to fully model all the possibilities quickly becomes a complex task. Shops usually have special hours around public holidays, they’ll often have different hours in summer and winter, and so on. An ideal solution would probably scope its opening time data with active dates, but I decided to go for something similar.

Instead of simply listing an opening and closing time each day, it makes sense to think of ‘openings’. A shop or cafe will open at one time, and close at another, hopefully on a pattern that fits into and repeats weekly. So the first step was to generate the model in rails and set up a migration:

create_table :openings do |t|
	t.integer :location_id, :opening_day, :closing_day
	t.time :opening_time, :closing_time
	t.integer :location_id
end

My migration also contained code to move the opening times currently stored in the locations table into the new openings table, and I used a script to update my fixtures file ready for refining the tests:

locations = YAML.load_file 'test/fixtures/locations.yml'
openings = {}

locations.each do |key, location|
  %w(monday tuesday wednesday thursday friday saturday sunday).each_with_index do |day, index|
    if location["#{day}_open"] and location["#{day}_close"]
      openings["opening_#{openings.size}"] = {
        'location_id' => location['id'],
        'opening_day' => index + 1,
        'closing_day' => index + 1,
        'opening_time' => location["#{day}_open"],
        'closing_time' => location["#{day}_close"]
      }
    end
  end
end

File.open("test/fixtures/openings.yml", 'w' ) { |out| YAML.dump(openings, out) }

So far, that’s fine, and once the associations are declared we can easily loop through all the openings and display their details. What about searching? How would I find all the places that are currently open?

Here we need a join, but it remains quite straightforward:

Location.find(:all, :include => :openings, :conditions =>
	['? BETWEEN openings.opening_time AND openings.closing_time AND
	? BETWEEN openings.opening_day AND openings.closing_day',
	Time.now.strftime('%H:%M'), Time.now.wday])

And really, that’s all there is to it. Depending on your usage you may want to encapsulate some queries in methods on your model (eg. Location.find_open) but it’s pleasing to be reminded of how simple ActiveRecord makes a project like this. Once you’ve worked out how to conceptualise the problem, it’s very little work to translate it to code and get it implemented.