MYSQL wrong time.now

Question

Hi there,

We got a Rails 3 Server based on Ruby 1.9.2 running on a MYSQL DB and Problems with the time.now and time.zone.now setting.

For better explaination we assuse its 13:00 UTC right now.

• Ubuntu Systemtime is set to 15:00 UTC +02:00 (Thats CEST (Central European Summertime) and is correct for our Timezone).

• The Rails time (time.now) is set to 13:00 +0200

• the time.zone.now is set to CEST
• MYSQL Global time and Session time are both set to SYSTEM

now we create a new entry in the MYSQL DB (via activerecord) which has a timestamp.

but when we look at the timestemp its -2 hours offset to the current server time (in our example 11:00)

We tried setting the time.zone to a very different one for testing propose, it had absolutly no effect on a new mysql entry. Also we tried to set the mysql global time and session time to UTC, no effect.

The only thing we can imagene right now what could be the problem is that the time.now is taking effect on the mysql entry and is set wrong. Problem here: we're unable to set the time.now setting. But even if that would be possible somehow (is it?), dunno if that fixes the problem.

Did anyone ever have such a problem or any ideas what could cause it?

Answer 1

There are two things to configure for rails.

1. The current timezone which you've done (used in Time.zone.now), this sets the timezone for timestamps in the instantiated records you load.

2. The timezone that those values are converted to when stored to the db. This should be set to the same timezone as your system/mysql. I think this option is configured via: ActiveRecord::Base.default_timezone = :utc

Since your system time is set to CEST, you'll want to use the same value for that as well.

The reason there are two config options is incase you've got users in multiple timezones (international audience), then Time.zone can be set differently for each logged in user, and the db timezone will convert correctly to the Time.zone timezone.