Wrong TimeZone Conversion in Ruby on Rails
mysql, ruby, ruby-on-rails, ruby-on-rails-3, ruby-on-rails-3.2
Solution
I'm pretty sure that the difference you're seeing is because of Daylight Saving Time (DST).
Note that all dates during summer are +0200, while all the others are +0100. That's the connection you didn't notice.
This is because Berlin is a timezone with DST. The dates you're importing are all UTC, they get written to the DB as UTC, but Rails (correctly) interprets them differently, depending on whether they're in summer or not.
That's why you don't get a constant 1-hour difference.
If you want the times to be 9:30 local time, no matter if in summer or winter, try using
Time.zone.parse("#{date} 09:30:00")
in your import code.
See ActiveSupport::TimeWithZone for details.
Problem
I'm really stucked on this one. Basically I want to Import a Excel-File. So I ended up using the CSV import from Rails. ``` CSV.open('[path-to-file]', 'r').each do |row| ``` Actually the Import is working fine, but there is among other things a Date-Column in the Excel/CSV-File and a DateTime-Column in the Database. I did following in the CSV.open-Method: ``` date = DateTime.strptime(row[0], "%Y-%m-%d").strftime("%Y-%m-%d") start_datetime = DateTime.parse(date + " 6:30:00").utc Event.create(:event_start => start_datetime) ``` This creates the correct Events with the correct DateTime in the Database. Looks for example like: ``` 2008-11-18 09:30:00 ``` My Problem: If I choose to comment the line in the application.rb ``` config.time_zone = 'Berlin' ``` Rails uses UTC to display my Events and everything looks like the content of the Database. If I choose to uncomment the config.time_zone part (what I definitely have to), Rails should add 1 hour (Berlin: UTC/GMT +1 hour). Actually it does add at least 1 hour, but sometimes 2 hours. There is no connection (for me), in which case Rails chooses to add 1 or 2 hours. If I create an Event on the normal Websurface (in the Browser), everything is working fine (subtract on 1 hour to save UTC in DB and add 1 hour to display in correct TimeZone). It would be really helpful, if you had some tips for me how I can try to localize this problem. My System: Rails 3.2.3 on Ruby 1.9.3p194, MySQL on Suse Enterprise Example: CSV: ``` 2012-01-08 2012-02-09 2012-03-10 2012-04-11 2012-05-12 2012-06-13 2012-07-14 2012-08-15 2012-09-16 2012-10-17 2012-11-18 2012-12-19 ``` DB (MySQL): ``` | id | event_start +----+--------------------- | 1 | 2012-01-08 06:30:00 | 2 | 2012-02-09 06:30:00 | 3 | 2012-03-10 06:30:00 | 4 | 2012-04-11 06:30:00 | 5 | 2012-05-12 06:30:00 | 6 | 2012-06-13 06:30:00 | 7 | 2012-07-14 06:30:00 | 8 | 2012-08-15 06:30:00 | 9 | 2012-09-16 06:30:00 | 10 | 2012-10-17 06:30:00 | 11 | 2012-11-18 06:30:00 | 12 | 2012-12-19 06:30:00 ``` View (Browser) - Here I just used an .order("event_start ASC") ``` events.each do |ev| ev.event_start 2012-12-19 07:30:00 +0100 2012-11-18 07:30:00 +0100 2012-10-17 08:30:00 +0200 2012-09-16 08:30:00 +0200 2012-08-15 08:30:00 +0200 2012-07-14 08:30:00 +0200 2012-06-13 08:30:00 +0200 2012-05-12 08:30:00 +0200 2012-14-11 08:30:00 +0200 2012-03-10 07:30:00 +0100 2012-02-09 07:30:00 +0100 2012-01-08 07:30:00 +0100 ```