5

Steps:

On my localhost server I select the time Jan 18, 2013 10 am - 11 am in my browser located in the Pacific time zone. I then select the exact same time on my production server on heroku.

As you can see the outputs of what is saved to the database are different. I want production to behave as localhost and take into account the timezone when storing the time as UTC. What is causing this? The heroku server is in EST, but that does not account for this behavior.

Controller:

  def create
    puts params[:event][:starts_at]
    @event = Event.create!(params[:event])
    puts @event.starts_at
  end

Output Localhost:

Fri Jan 18 2013 10:00:00 GMT-0800 (PST)

2013-01-18 18:00:00 UTC

Output Heroku (production):

Fri Jan 18 2013 10:00:00 GMT-0800 (PST)

2013-01-18 10:00:00 UTC

Schema:

 t.datetime "starts_at"

Env:

Ruby on Rails 3.2.11

Ruby 1.9.3

Postgres

John
  • 4,362
  • 5
  • 32
  • 50
  • What's your timezone config in `config/application.rb` ? – Thong Kuah Jan 13 '13 at 10:50
  • @ThongKuah I have it commented out, which the comments say means it is UTC since it is UTC by default. – John Jan 13 '13 at 23:35
  • Hey John, is this somehow related to http://stackoverflow.com/questions/2719330/why-does-heroku-log-using-the-server-time-rather-than-the-rails-time-zone?rq=1 I have seen weird tz problems with Heroku before. – Saran Jan 14 '13 at 01:58
  • @Saran No luck with that answer's solution. It seemed to pertain more to logging times. – John Jan 14 '13 at 05:53
  • @John run `\d your_table` in Heroku and localhost ? Do you have timestamp without time zone as your postgres date type ? – Thong Kuah Jan 14 '13 at 07:45
  • @ThongKuah Yes they have the timestamp without time zone as the postgres data type. How do I fix this? Thanks for the tip! – John Jan 15 '13 at 04:46

2 Answers2

3

Background on how PostgreSQL deals with date/time

I think your issue is covered in this answer from another SO question:

Specifically this answer:

Use this query for time in UTC format:

SELECT * FROM tbl WHERE time_col > (now() AT TIME ZONE 'UTC')::time

Use this query for time in local timezone:

SELECT * FROM tbl WHERE time_col > now()::time

Rails & ActiveRecords & date/time

This rails/rails github issue also discusses it:

There is a comment by pixeltrix which says the following:

@deathbob sorry for the late reply - the accepted values for config.active_record.default_timezone are either :utc or :local. If you set it to something other than :utc it will assume :local so what you're seeing is the expected behavior.

To fix the time in ActiveRecords so that it is always dealt with as UTC you can set the following variable:

config.active_record.default_timezone = :utc
Community
  • 1
  • 1
slm
  • 15,396
  • 12
  • 109
  • 124
  • Sorry I was out of town, and didn't get to this till now. I think your answer is on the right track, but I'm still somewhat confused. Is there an activerecord solution? I don't use Sequel. The two SQL queries you provided are selects, but I'm most concerned with inserting the datetime correctly. Thanks! – John Jan 24 '13 at 07:57
  • config.active_record.default_timezone = :utc – slm Jan 24 '13 at 08:14
0

The solution by @slm of config.active_record.default_timezone = :utc did not work for me. Rails was still writing with local and reading with UTC on Heroku.

So I then tried:

config.time_zone = 'Sydney'
config.active_record.default_timezone = :local

Which was still working fine in development but caused the datetime values to now be read in a UTC format from postgres on Heroku.

My temporary workaround solution was to set the default timezone on Heroku with:

heroku config:add TZ="Australia/Sydney"

This is only a temporary solution as I would like my app to use UTC. I have posted to the rails/github issue mentioned by @slm for clarification. I will update my answer when I find a more concrete solution.

Marklar
  • 1,247
  • 4
  • 25
  • 48