We uncovered a Strange Thing when working on Omahype’s event listings.
Omahype is a curated events calendar helping to foster art and culture in Omaha and the surrounding areas. If you’re in the area, you should definitely check it out!
The Strange Thing
Events, which are ActiveRecord models, have a
start_time field which we use to show people when the event starts.
We also use this field to distinguish past events from upcoming ones. The site’s category views call
Event.upcoming to get only events from today and into the future. It looks something like this:
def self.upcoming where("start_time >= ?", Date.today).order("start_time asc") end
That looked pretty good to me, but once the site was in production we noticed a problem.
The category views were displaying last night’s events in addition to the ones we wanted. Troublesome. I added a test case1 which reproduces the behavior:
describe Event do describe ".upcoming" do it "doesn't match events from last night" do last_night = Time.now.beginning_of_day - 2.hours create :event, start_time: last_night expect(Event.upcoming.count).to eq 0 end end end
That test failed, but why is that?
The query says that the start time must be greater than or equal to today’s date and that’s surely not the case with last night’s events. Or is it?
ActiveRecord stores all timestamps in the database as UTC and converts back and forth between the applicable time zone in Ruby land before executing queries.
Usually this works just fine with timestamp comparisons because the offsets are adjusted before the database makes the comparison, but it doesn’t work so well when you are comparing against just a date.
Omahype only tracks events in Omaha so we have Rails configured to use US Central Time (CST):
# config/application.rb module Omahype class Application < Rails::Application config.time_zone = "Central Time (US & Canada)" end end
The problem is that CST is UTC-6 (or -5 during daylight savings), so any event created with a
start_time of 6pm or later will be stored in the database as bright and early on the following morning.
To be clear, when we do this in Ruby:
Event.create start_time: Time.parse("2013-02-19 20:00:00")
In the database (Postgres, in our case), it will be stored as a timestamp without time zone of “2013-02-20 02:00:00”.
See the problem?
In UTC that event does start today even though in CST it starts last night. ActiveRecord can adjust the right-hand side of the comparison to UTC before executing the query, but it can’t do anything about the left-hand side.
Converting the right-hand side to UTC doesn’t help us because the date remain the same.
The (not so great) Solution
We decided to convert the left-hand side of our comparison from UTC to CST.
In Postgres you can use
AT TIME ZONE to explicitly set the time zone which applies to a timestamp column. With this in hand, I adjusted the method to look like this:
def self.upcoming where("start_time at time zone 'CST' >= ?", Date.today).order("start_time asc") end
Unfortunately, my test was still red.
This confused me to no end, but it turns out that
AT TIME ZONE only converts the time value when it is applied to a
timestamp with time zone field not a
timestamp without time zone field, which
Here’s their chart of how it works for different field types:
So I had to first convert
start_time to a
timestamp with time zone of UTC and then convert it from UTC to CST. Like this:
def self.upcoming where("start_time at time zone 'UTC' at time zone 'CST' >= ?", Date.today).order("start_time asc") end
That is pretty ugly if you ask me, but the test was green.
The Alternatives (?)
There are a bevy of problems that I have with this solution.
I hardcoded ‘CST’ into the query, which is okay for this app but would not work if we were switching time zones based on user location or any other criteria.
The two calls to
at time zoneslow down the query by a smidgeon. Not much, but enough to not like it.
Other finder methods will have to also use this workaround or they, too, will return bad results.
I’d love to hear of others way to tackle this problem. One thing I thought of would be to store a separate
start_date field alongside the
start_time. That would make all comparisons apples-to-apples (dates-to-dates), but that is inelegant because it requires us to manage the two fields in the code and ensure they aren’t different.
Perhaps there is a smarter way to perform the same type of query? Or maybe I’m missing some obvious time zone conversion thing in ActiveRecord that obviates the need for db-side conversions?
Please let me know if you have any insight on this curious case!
I would have caught it sooner, but my original tests didn’t cover the edges well enough. They used events from 10 days ago instead of last night. ↩