Rails. How to store time of day (for schedule)?

Question

I'm writing an app that keeps track of school classes.

I need to store the schedule. For example: Monday-Friday from 8:am-11am.

I was thinking about using a simple string column but I'm going to need to make time calculations later.

For example, I need to store a representation of 8am, such as start_at:8am end_at:11am

So how should I store the time? What datatype should I use? Should I store start time and number of seconds or minutes and then calculate from there? or is there an easier way?

I use MySQL for production and SQLite for development.

Answer 1

Suggestion:

Don’t worry about a specific datatype for that. A simple solution would be:

1. In the database, add an integer type column for start_time and another for end_time. Each will store the number of minutes since midnight.
   Ex: 8:30am would be stored as 510 (8*60+30)

2. In the form, create a select field (dropdown) that displays all available times in time format:
   Ex.: 10am, 10:30am and so on.
   But the actual field values that get saved in the database are their integer equivalents:
   Ex: 600, 630 and so on (following the example above)

Answer 2

This ruby gem converts time of day to seconds since midnight and back. The seconds value is stored in the database and can be used for calculations and validations.

Define the time of day attributes:

class BusinessHour < ActiveRecord::Base
  time_of_day_attr :opening, :closing
end

Converts time of day to seconds since midnight when a string was set:

business_hour = BusinessHour.new(opening: '9:00', closing: '17:00')
business_hour.opening
 => 32400
business_hour.closing
 => 61200

To convert back to time of day:

TimeOfDayAttr.l(business_hour.opening)
 => '9:00'
TimeOfDayAttr.l(business_hour.closing)
 => '17:00'

You could also omit minutes at full hour:

TimeOfDayAttr.l(business_hour.opening, omit_minutes_at_full_hour: true)
 => '9'

Answer 3

Check out the gem 'tod' for Rails 4 or Time_of_Day for Rails 3. They both solve the problem of storing time in a database while using an an Active Record model.

SQL has a time data type but Ruby does not. Active Record addresses this difference by representing time attributes using Ruby’s Time class on the canonical date 2000-01-01. All Time attributes are arbitrarily assigned the same dates. While the attributes can be compared with one another without an issue, (the dates are the same), errors arise when you attempt to compare them with other Time instances. Simply using Time.parse on a string like ”10:05” adds today’s date to the output.

Lailson Bandeira created a created solution for this problem, the Time_of_Day gem for Rails 3. Unfortunately the gem is no longer maintained. Use Jack Christensen’s ‘tod’ gem instead. It works like a charm.

Answer 4

If you're using Postgresql you can use a time column type which is just the time of day and no date. You can then query

Event.where("start_time > '10:00:00' and end_time < '12:00:00'")

Maybe MySQL has something similar

Answer 5

I made an app recently that had to tackle this problem. I decided to store open_at and closed_at in seconds from midnight in a simple business hour model. ActiveSupport includes this handy helper for finding out the time in seconds since midnight:

Time.now.seconds_since_midnight

This way I can do a simple query to find out if a venue is open:

BusinessHour.where("open_at > ? and close_at < ?", Time.now.seconds_since_midnight, Time.now.seconds_since_midnight)

Any tips for making this better would be appreciated =)

Answer 6

I would store the starting hour and the duration within the database, using two integer columns.

By retrieving both values, you could convert the starting hour as in (assuming that you know the day already:

# assuming date is the date of the day, datetime will hold the start time
datetime = date.change({:hour => your_stored_hour_value , :min => 0 , :sec => 0 })

# calculating the end time
end_time = datetime + your_stored_duration.seconds

Otherwise, hava a look at Chronic. The gem makes handling time a little bit easier. Note that the changemethod is part of rails, and not available in plain Ruby.

The documentation on DateTime for plain Ruby can be found here.

Also, whatever you do, don't start storing your dates/time in 12-hour format, you can use I18nin Rails to convert the time:

I18n.l Time.now, :format => "%I.%m %p",  :locale => :"en"
I18n.l Time.now + 12.hours, :format => "%I.%m %p",  :locale => :"en"

You can also get from this notation, that you can store you duration in hours, if you want, you can then convert them rather easily by:

your_stored_value.hours

if stored as an integer, that is.

Answer 7

From the SQLite 3 website,

"SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS"). REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar. INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions."

You can then manipulate the values using the Date and Time functions outlined here.

Answer 8

I assume you are using some kind of database for this. If you are using MySQL or Postgresql, you can use the datetime column type, which Ruby/Rails will automatically convert to/from a Time object when reading/writing to the database. I'm not sure if sqlite has something similar, but I imagine it probably does.