Ruby on Rails: Why am I getting the time clock when I write the time to the DB and then read it?
I have config.time_zone in environment.rb set to "UTC" and my mySQL server returns the current time in my local timezone when I issue "select now ()"; and in utc when I ask "select utc_timestamp;"
I am running rails 2.1.2, mysql gem 2.7.3, activerecord gem 2.1.2 and mysql --version returns "Ver 14.12. Distribution 5.0.27 for Win32 (ia32)".
EDIT: My environment.rb is set to UTC and has been since I started the project. Rebooting the server would not replace any 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 is causing this?
source to share
We had the same problem regarding dates, time zones and MySQL. The latter assumes that you are providing it with date / time values in the timezone it is configured with.
But, since you've configured Rails to handle UTC times, ActiveRecord will convert any date / time values to UTC (thus Tue Nov 25 17:40:48 -0800 2008 becomes Wed Nov 26 01:40:48 0000 2008) to using the value in the SQL update / create query that it generates and sends to MySQL.
In pseudocode
("time = %t", Tue Nov 25 17:40:48 -0800 2008) => "time = '2008-11-26 01:40:48'
which is considered MySQL 2008-08-26 01:40:48 -0800 .
Take a look at your debug log file and you can see what I mean. The only way it can work fine (which means no nasty surprises) is to set the same timezone in Rails AND MySQL, that timezone is UTC. This is the configuration we are using.
source to share
Not the answer you probably want, but I found the key to getting congruent times to handle all the timezone based logic in Rails land and maintain a silly database.
This went back to rails 2.0 where the timezones / utc implementation was incredibly buggy / broken, so it could be better now.
source to share