Ruby on Rails and Active Record standalone scripts disagree on database values for :timestamps

Question

I posted a question earlier today when I'd not zeroed in quite so far on the problem. I'll be able to be more concise here.

I'm running RoR 2.1.2, on Windows, with MySQL. The SQL server's native time zone is UTC. My local timezone is Pacific (-0800)

I have a model with a :timestamp type column which I can do things like this with:

record = Record.find(:first)
record.the_time = Time.now()

When I do a "select * from records" in the database, the time shown is eight hours in advance of my local time, which is correct, given that the DB is on UTC. (I have verified that it is 'thinking in utc' with a simple 'select now()' and 'select utc_timestamp()')

This is where the trouble begins. If I display the time in a view:

<%= h record.the_time %>

...then I get back the correct time, displayed in UTC format. If I wrote to the database at 16:40:00 local time, the database showed 00:40:00.

HOWEVER, if I am running a standalone script:

record = Record.find(:first)
puts record.the_time

...then I get back the UTC time that I stored in the database (00:40:00,) but with the local timezone:

Wed Nov 26 00:40:00 (-0800) 2008

...an eight-hour time warp. Why is it that storing the time translates it correctly, but retrieving it does not? If I compare a stored time from the recent past in the DB and compare it to the current time, the current time is less - telling me this isn't just a string conversion issue.

Any ideas?

Answer 1

Well, you did not comment on my last suggestion to restart your server or your console.

I had a similar problem and it got fixed when I did so.

Also you mention a stand-alone script, but in order to get the time-zone feature of Rails the code needs to run within Rails.

Answer 2

There is a setting in config/environment.rb that sets a time_zone. Possibly this is not set the same in your script:

# Make Time.zone default to the specified zone, and make Active Record store time values
# in the database in UTC, and return them converted to the specified local zone.
# Run "rake -D time" for a list of tasks for finding time zone names. Uncomment to use default local time.
config.time_zone = 'UTC'

You can also try explicitly specifying the TZ and format:

require 'active_support/core_ext/date/conversions'
record.the_time.utc.to_s(:db)

(or cheat and grab the code fragment from there if you're not using active_support in your standalone script)

Answer 3

Try requiring active_support

Answer 4

In the end, it was far easier to do this by storing times as an integer (Time.now().to_i()) and converting them back to a time when I needed to display them (Time.at(the_time_the_database_returned_to_me).) This is hardly the best solution, but it was the only one that worked.