No It’s DateTime!

Jay Hayes

4 min read

Mar 12, 2015

No It’s DateTime!

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

They said.

“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.

An Example

Let’s set the stage. Pretend we have an app with a very simple
data model. It consists of a single model, Plan,
which has a single datetime attribute going_out_at.

Our Goal

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.

Our First Try

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:
  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.

Our Grand Disappointment

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:
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 datetime type.

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…

The Solution

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 =
=> Thu, 25 Sep 2014 05:00:00 EDT -04:00
> to_time =
=> 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
parse using!

Time is Hard

Let’s face it, time is hard. Being vigilant helps you watch out
for these crazy time zone-related quirks!

Editor’s note: A version of this post first appeared on Jay’s blog.

Mark Dalrymple

Reviewer Big Nerd Ranch

MarkD is a long-time Unix and Mac developer, having worked at AOL, Google, and several start-ups over the years.  He’s the author of Advanced Mac OS X Programming: The Big Nerd Ranch Guide, over 100 blog posts for Big Nerd Ranch, and an occasional speaker at conferences. Believing in the power of community, he’s a co-founder of CocoaHeads, an international Mac and iPhone meetup, and runs the Pittsburgh PA chapter. In his spare time, he plays orchestral and swing band music.

Speak with a Nerd

Schedule a call today! Our team of Nerds are ready to help

Let's Talk

Related Posts

We are ready to discuss your needs.

Not applicable? Click here to schedule a call.

Stay in Touch WITH Big Nerd Ranch News