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.


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.


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


Need Your Help

Using statement for directory search or Ldap functionality in vb.net

asp.net vb.net

i am very new to VB.net. Can i use the USING Statement for calling the Directory Entry and Searcher like the below.

AS3. How to change character's template in adobe flash cs5 using AS3?

actionscript-3 flash templates adobe character

I'm creating flash game. Here will be abillity to choose one of two (or more) character's. So I have in library created symbol hero. It have 7 animations on click (moving, jumping, attacking etc..)...