Weird time inconsistencies between production and development

Question

For some reason times are appearing differently in development (my local Mac) and production (Heroku). Take a look: (Just prior to doing this I did a heroku db:pull, so the databases should be identical)

Production (Heroku)

>> Annotation.last.id
=> 2028
>> Annotation.last.created_at
=> Sat, 12 Sep 2009 06:51:33 UTC +00:00
>> Time.zone
=> #<ActiveSupport::TimeZone:0x2b4972a4e2f0 @tzinfo=#<TZInfo::DataTimezone: Etc/UTC>, @utc_offset=0, @name="UTC">
>> Time.now.zone
=> "PDT"

Development (my Macbook Pro)

>> Annotation.last.id
=> 2028
>> Annotation.last.created_at
=> Sat, 12 Sep 2009 09:51:33 UTC +00:00
>> Time.zone
=> #<ActiveSupport::TimeZone:0x23c92c0 @tzinfo=#<TZInfo::DataTimezone: Etc/UTC>, @utc_offset=0, @name="UTC">
>> Time.now.zone
=> "EDT"

Since the created_at times differ by 3 hours, I assume this is related to the 3 hour difference between EDT and PDT, but I'm not sure what's going on.

EDIT: Here's what the raw data looks like:

sqlite> Select created_at from annotations where id = 2028;
2009-09-12T09:51:33-04:00

Answer 1

Looks like it assumes the dates in the DB are stored in your local time-zone which is different for the 2 environments and then it translates it to UTC. since the value in DB is essentially the same you get 2 different UTC values.

What is the value of config.time_zone from your "config/environment.rb"? Also what is the value of "Select created_at from annotations where id= 2028" ?

Answer 2

This is a problem I've encountered before as well.

If you do something like Annotation.last.created_at on the rails console, the result already has the timezone applied to it. You should look at the "pure" date in mysql, via the mysql console :-)

Answer 3

Perhaps one or both of the machines has it's system time setting set to local timezone, instead of UTC. If that's the case, then it would be confused on what value the UTC time (int) actually is.

There are a number of areas to check on both machines:

• system timezone
• local(user) process timezone
• database system timezone
• database local timezone

Answer 4

This doesn't seem explicable by one or the other reporting its local time as the UTC time: Eastern time is 4 hours away from UTC, and Pacific time is 7 hours away, but you're seeing a three-hour difference, not a 4- or 7-hour difference.

It seems like both of them are producing incorrect output. SQLite is clearly saying that the 09:51 time is supposed to be an -04:00 time, i.e. Eastern time, but Rails is incorrectly claiming that it's UTC in one case — and in the other case, it's translating it from Eastern to Pacific, then incorrectly claiming that the result is UTC.

Maybe this is a bug in the SQLite backend for ActiveRecord? Because it seems like the kind of thing that would have been caught and squished long ago if it were in widely-used code.

Answer 5

It looks like this is a problem when moving databases between Heroku and your development machine. Running the SQL query directly gives me this:

Local: {"created_at"=>"2009-10-30 22:34:55.919586"}

Remote: {"created_at"=>"2009-10-31 01:34:55.919586"}

Same problem, exactly a three hour shift. Looking through the source for Taps, the Gem heroku uses for DB sync, does not provide any clues into what could be going wrong here. I've opened a heroku support ticket, and I'll update this post with what I find.

UPDATE: Ricardo and Morten from Heroku replied. Specify the TZ on the command line like so:

TZ=America/Los_Angeles heroku db:pull