— Oct 16, 2014
A while back, I got all fluffy talking about love and coffee and all that other stuff. Programming isn’t all butterflies and rainbows, folks.
Eventually it will happen. You’re going to have to deal with time zones.
Learn from my mistakes.
Always parse user input time.
Not long ago, I was happily working away on a client project when a feature came along.
time should also go Eastern US instead of UTC
“No problem,” I thought. “We’ll configure the application’s time zone to use Eastern time and go on our merry way.”
Generally, this is true, but there’s a big catch when you’re taking user input time and throwing it against the database.
Let’s set the stage. Pretend we have an app with a very simple
data model. It consists of a single model,
which has a single
The goal is simple. We want users to be able to find plans to go out between two points in time. How they enter this information is irrelevant, but we can assume that the parameters will be parsable time strings.
We create a range using the user input values and query the database.
> Plan.all => [#<Plan id: 1, going_out_at: "2014-09-25 09:39:30">] > from = '2014-09-25 09:00' > to = '2014-09-25 10:00' > Plan.where(going_out_at: from..to) SELECT "plans".* FROM "plans" WHERE ("plans"."going_out_at" BETWEEN '2014-09-25 09:00' AND '2014-09-25 10:00') => [#<Plan id: 1, going_out_at: "2014-09-25 09:39:30">]
By default, Rails apps are configured to use the UTC time zone. This also happens to be the time zone (or lack thereof) that the database stores things in.
Due to this coincidence, using the input times as strings works fine when sent directly to the database. That is, they match the database’s format and return the expected results without having to cast the values. Unfortunately, the database doesn’t expect to be given zoned times in this query.
This is where we attempt to localize our app to our time zone. We whip out
application.rb and set the app’s time zone.
config.time_zone = 'Eastern Time (US & Canada)'
With this configuration set, times in the app will be automatically localized to the application’s timezone.
> Plan.first.going_out_at => Thu, 25 Sep 2014 05:39:30 EDT -04:00
Now, let’s have a look at the same scenario as above in Eastern U.S. time.
You can see the date stored in the database is UTC:
> Plan.all => [#<Plan id: 1, going_out_at: "2014-09-25 09:39:30">]
The user inputs the following times which present as 09:00-10:00 in UTC.
> from = '2014-09-25 05:00 -04:00' > to = '2014-09-25 06:00 -04:00' > Plan.where(going_out_at: from..to) SELECT "plans".* FROM "plans" WHERE ("plans"."going_out_at" BETWEEN '2014-09-25 05:00 -04:00' AND '2014-09-25 06:00 -04:00') => 
As you can see, the query returns an empty set because the database doesn’t know to interpret these values as zoned times. Let’s jump into the database to prove our theory.
sqlite> SELECT "plans".* FROM "plans" ...> WHERE "plans"."going_out_at" ...> BETWEEN '2014-09-25 05:00 -04:00' AND '2014-09-25 06:00 -04:00'; # nothing here...
In order to query using zoned times, we must explicitly cast the times as the
sqlite> SELECT "plans".* FROM "plans" ...> WHERE "plans"."going_out_at" ...> BETWEEN datetime('2014-09-25 05:00 -04:00') AND datetime('2014-09-25 06:00 -04:00')); 1|2014-09-25 09:39:30.961636
So how do we fix this issue in our app? Do we need to cast these columns in our query? Yuck…
Thankfully, the solution is relatively simple. Always deal in date and time objects. This allows Rails to do the heavy lifting of making sure queries get zoned in a way that is compatible with the database. Check it out.
> from_time = Time.zone.parse(from) => Thu, 25 Sep 2014 05:00:00 EDT -04:00 > to_time = Time.zone.parse(to) => Thu, 25 Sep 2014 06:00:00 EDT -04:00 > Plan.where(going_out_at: from_time..to_time) SELECT "plans".* FROM "plans" WHERE ("plans"."going_out_at" BETWEEN '2014-09-25 09:00:00.000000' AND '2014-09-25 10:00:00.000000') => [#<Plan id: 1, going_out_at: "2014-09-25 09:39:30">]
You can see that when the range’s values are zoned time, Rails takes care of
converting them to UTC for the database queries. Just make sure you
Let’s face it, time is hard. Being vigilant helps you watch out for these crazy time zone-related quirks!