Ruby on Rails: Why do I get timezones munged when I write a time to the DB, then read it back?

Question

I have config.time_zone in environment.rb set to "UTC", and my mySQL server returns the current time in my local time zone when I issue "select now();" and in utc when I ask for "select utc_timestamp;"

I'm running rails 2.1.2, the mysql gem 2.7.3, activerecord gem 2.1.2, and mysql --version returns "Ver 14.12 Distrib 5.0.27 for Win32 (ia32)".

EDIT: My environment.rb is set to UTC and had been since I started the project. A server restart would have picked up no changes.

record = Record.find(:first)
puts Time.now
# Tue Nov 25 17:40:48 -0800 2008
record.time_column = Time.now
record.save

mysql> select * from records;
---------------------
 2008-11-26 01:40:48

#note that this is the same time, in UTC.

record = Record.find(:first)
puts record.time_column
Wed Nov 26 01:40:48 -0800 2008

#NOTE that this is eight hours in advance!  
#All I've done is store a date in the database and retrieve it again!

Any ideas what causes this?

Answer 1

After editing the environment.rb file, did you restart your server before making a new record to your database?

Answer 2

We had the same issue regarding dates, time zones and MySQL. The latter assumes you provide it with date/time values in the timezone it's configured with.

But, since you configured Rails to handle time in UTC, ActiveRecord converts any date/time values in UTC (thus Tue Nov 25 17:40:48 -0800 2008 becomes Wed Nov 26 01:40:48 0000 2008) before using the value in the SQL update/create query it generates and sends to MySQL.

In pseudo-code

("time = %t", Tue Nov 25 17:40:48 -0800 2008) => "time = '2008-11-26 01:40:48'

which is considered as 2008-11-26 01:40:48 -0800 by MySQL.

Look at your debug log file and you'll see what I mean. The only way it can properly work (meaning w/o bad surprises) is by setting the same timezone in Rails AND MySQL, this timezone being UTC. This is the configuration we use.

Answer 3

Not an answer you probably want, but I found the key to getting congruent times was to handle all timezone based logic in Rails land and keep the database stupid.

This was going back to rails 2.0 where the timezones/utc implementation was incredibly buggy/broken though, so it might be better now.