Correctly storing dates with TimeZone in MySQL DB for a Rails application

Question

I'm looking at converting our Rails 2.3 application to correctly handle time zones (at the moment everything is in UTC which isn't right, but is convenient!).

I have these settings in environment.rb:

config.active_record.default_timezone = :utc
config.time_zone = "UTC"

Going forward, on each request in our app I plan on making the following setting to set the Time Zone:

Time.zone = user.time_zone

Where user.time_zone is their chosen preference (e.g. US Pacific Time).

This works fine in the app, but my question relates to what Rails then stores in the MySQL DB. If the user chooses a date of 1st June 2009, this gets stored in a DATETIME field in the database in UTC but with the time zone offset. For example, if the user has selected a GMT+6 time zone, a chosen date of 1st June 2009 ends up in the database as 2009-06-01 06:00:00 UTC.

I would have expected this to be stored as 2009-06-01 00:00:00 UTC in the database. Is my thinking correct or is Rails doing something unexpected here?

Answer 1

You need to set the timezone in the data object before you set the date.

Answer 2

Based on your response to my comment, I'd say just store the date based on the local server time, then use JavaScript's Date::toLocaleString() to convert it to their local timezone. I wrote an article on it a couple of years ago, which you can find here.

The article's JS was written in MooTools, but I've since rewritten it with jQuery, so I'll show that code.

The important parts:

When rendering to HTML, use the milliseconds since Epoch.

<span class="dt"><!-- <%= blah.created_at_epoch_ms %> --><%= blah.created_at %></span>

Which requires a method in your model defined like this:

def created_at_epoch_ms
    self.created_at.to_i * 1000
end

The JS to transform the dates:

$(document).ready(function(){
        $('span.dt').each(function(){
                var date = new Date();

                date.setTime(this.firstChild.data);

                $(this).parent().text(date.toLocaleString());
        });
});

This should convert the string to the user's local time, and the magic happens in toLocaleString(). The only browser I've heard of that doesn't implement it is Safari 2.0, which shouldn't present much of a problem, as I think most users have moved on.

I use this method on my site, and if you look at the source code to the page, you can see what it does. The actual code that's sent to the browser looks like:

<!-- 1243484521000 -->2009-05-28 04:22:01 UTC

Which is converted in my browser (Central Time) to

Wednesday, May 27, 2009 11:22:01 PM

If they have JS enabled, it'll take the value of the comment node, transform it, and replace the entire string (including the UTC time) with the local time. If JS is disabled, they'll just see the time as the server sees it.

Answer 3

This blog post talks about native Time zone handling in Rails 2.1+:

http://mad.ly/2008/04/09/rails-21-time-zone-support-an-overview/

The gist is that Rails will store all records in the DB in UTC and then convert to the users timezone for display. Which makes sense: store all data in a neutral form, and then convert it to the desired form at the last minute.