Working with the SQL ‘time’ type in Ruby on Rails
There have been many great articles written over the years about working with time and timezones in Rails. But few, if any, spend much time discussing the concept of a ‘time of day’ with no date or time zone attached.
During a recent project at ezCater, we migrated some ‘time of day’ data from being stored as integers (representing ‘military time’) to using PostgreSQL’s 'time without time zone' type. This type stores time values in HH:MM:SS format (24-hour clock) with no additional date or time zone info.
Using a time-specific data type meant that we could now rely on Rails to handle the object wrangling for us - no more custom logic to turn '1600' into a timestamp, and vice versa. However, while both Ruby and Rails have classes to handle specific points in time (a date, or date + time + time zone), using them to handle a general ‘time of day’ is like trying to fit a square peg into a round hole.
Accessing and storing time data
As of Rails 5.1, the default behavior is to treat a SQL ‘time’ value as a ‘timezone aware’ timestamp. This means that Active Record will load it using an instance of ActiveSupport::TimeWithZone, slap a date onto it (Jan 1, 2001), and assume that the value was stored in UTC [1]. When accessing the value, Active Record will convert it into your application’s configured time zone. This can lead to some unexpected results for your ‘time of day’: (Note: all examples use "Eastern Time" for the app's configured time zone)
Our wake_up_at value is 2pm...
... but in Rails, it's 9am.
When saving data, if an instance of Time/TimeWithZone is used to hold the desired value, Rails will convert the time to UTC before saving to the database.
We want to wake up at 2pm...
...but in the database, we now have 7pm.
If a string is used for the desired value, Rails will parse the string using the application’s configured time zone, then convert that time to UTC.
We want to wake up at 2pm...
...but in the database, we now have 7pm.
In short, the default accessing and storing behavior for ‘time of day’ values leaves a lot of room for confusion. Let’s take a look at some ways to improve this!
Modifying Rails Configuration
Active Record gives us some hooks to override the default time conversion behavior. You can specify that only datetime columns (and not time) should be considered ‘timezone aware’:
ActiveRecord::Base.time_zone_aware_types = [:datetime]
Or if global whitelisting isn’t your cup of tea, you can configure this on a per-column basis:
class MyClass < ApplicationRecord
self.skip_time_zone_conversion_for_attributes = [:column_name]
end
With these changes, Active Record will instead load your time value using an instance of Ruby’s Time class, and will not convert the time into your app’s time zone when accessed. It will still add a date (again Jan 1, 2001) and time zone offset (UTC), and will still allow you to convert the time to other time zones.
When saving data, using an instance of Time/TimeWithZone will behave the same as before, with Rails converting the time to UTC before saving it. However, using a string will behave differently; instead of parsing the string in the application’s time zone, it parses it in UTC.
While this type of configuration does make time-related behavior a bit more intuitive, it still adds some non-trivial complexity to the concept of a simple ‘time of day’. We can do better!
The ezCater Solution
We came across the tod gem when looking for a solution to our time woes. It had nice parsing capabilities for both Time and string values, a familiar API for formatting string output, and felt like an intuitive way of representing a simple ‘time of day’.
To wire this up to Rails, we added a Railtie to the gem with an initializer to register a new Active Record type. The new type loads database values as an instance of TimeOfDay, and uses the gem’s Time/string parsing to serialize data for storage. In our app, we can now tell our model to use the new type for our ‘time of day’ columns:
class Person < ApplicationRecord
attribute :wake_up_at, :time_only
attribute :sleep_at, :time_only
end
This approach eliminates the overhead of working with dates and time zones, and makes working with a simple ‘time of day’ much nicer:
Set Person 1 to wake up at 2pm with a string
Set Person 2 to wake up at 2pm with a TimeWithZone object
Everyone wakes up at the right time!
Querying time data
After setting up the new attribute type for our time data, we found that writing Active Record queries was another place where we could take advantage of the tod gem’s time-parsing abilities. Consider the following scope:
scope :awake, ->(time) do
where("wake_up_at <= :time AND sleep_at > :time", time: time)
end
If this scope is called with an instance of Time/TimeWithZone, Active Record will convert the timestamp to UTC when generating the SQL statement. This puts the burden on the caller to know the correct way to set up their time object, and can lead to unexpected output:
Person.awake(Time.zone.parse("14:00:00")).to_sql"SELECT \"people\".* FROM \"people\" WHERE (wake_up_at <= '2019-05-06 18:00:00' AND sleep_at > '2019-05-06 18:00:00')"
Calling the scope with a string will inject that string as-is into the generated SQL. This is a bit more intuitive, but still requires the caller to know how to generate a time string representing the correct time in the correct format.
Instead of letting the caller do the work, we can load the provided time value using the gem's TimeOfDay class, and then pass that object to Active Record. All that was required was a little extra plumbing to give Active Record a value to use when building the SQL statement [2]. Now the scope becomes much more intuitive and flexible:
scope :awake, ->(time) do
where(
"wake_up_at <= :time AND sleep_at > :time",
time: Tod::TimeOfDay.load(time)
)
end
Person.awake(Time.zone.parse("14:00:00")).to_sql Person.awake("14:00").to_sql
Both produce:
"SELECT \"people\".* FROM \"people\" WHERE (wake_up_at <= '2019-05-06 14:00:00' AND sleep_at > '2019-05-06 14:00:00')"
Hopefully this has helped shed some light on handling ‘time of day’ values in Rails. Thank you for reading!
[1] By default, Active Record will assume times in the database are stored in UTC, since time values are not stored with timezone information. You can configure this by setting config.active_record.default_timezone... but don’t say I didn’t warn you.
[2] We chose a simple implementation here (as opposed to the Railtie approach) because of the gem’s pre-existing ties to Active Record.
Huge thank you to the Buffalo Wild Things squad, Brent Wheeldon, and Alex Robbin!
