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

How do I show a fragment from an Android custom component?

java android android-fragments

I've got a custom component declared using merge tags that is used in several locations in my application. Is it possible to call getFragmentManager() or getSupportFragmentManager() from this class...

Migrating wordpress server and use the old database

php mysql wordpress phpmyadmin

I have to migrate the files from a website in Wordpress to a new server, however I would like the database to stay on the old server.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.