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

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?

Answers


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.


Need Your Help

Iterating through Arraylist with extended classes

java class iteration

I have been wondering about a function in Java, which I hope exists. I want to iterate through an Arraylist (say of class A), which contains objects of class B and C which both extend A. The idea, ...

Using MSSQL CE in C# getting error, and can't figure our why,

c# sql-server sql-server-ce

I have a problem with inserting into a MSSQL CE database,