1

I have my app time zone set to Pacific. 

# application.rb: 
config.time_zone = 'Pacific Time (US & Canada)'

My production server time is set to Pacific. But ActiveRecord sets the per-session postgres time zone to 'UTC': 

[1] pry(main)> ActiveRecord::Base.connection.execute("show timezone").getvalue(0,0)
  (0.2ms)  show timezone
=> "UTC"

The docs say my only two options for config.active_record.default_timezone are :utc and :local

Using :local is fine for production, but my development server is in a different time zone.

I know it's often recommended to leave all time zone settings to the default UTC, but my app uses postgres's time and date functions extensively for ETL jobs, complex data analysis queries, etc. I would rather have postgres be my "single source of truth" than rails/ruby, so I'm not interested in answers that tell me to do everything in UTC

Edit: I've confirmed that config.time_zone is properly set: 

[6] pry(main)> Rails.application.config.time_zone
=> "Pacific Time (US & Canada)"

and config.active_record.default_timezone is set to :local 

[9] pry(main)> Rails.application.config.active_record.default_timezone
=> :local

in the same pry session:

[10] pry(main)> ActiveRecord::Base.connection.execute("show timezone").getvalue(0,0)
   (0.2ms)  show timezone
=> "Asia/Bangkok"
sheepdog
  • 625
  • 5
  • 19
  • It sounds at first blush that it is local to the system, but it looks [from reading](https://stackoverflow.com/a/32229086/525478), that it may be local to the app. Have you verified that config.timezone to the correct zone, and default_timezone to :local doesn't actually work, independent of the system time zone? I suspect it will. We do this a lot, but the system time is usually set to the preferred timezone, too. – Brad Werth Sep 27 '18 at 05:33
  • @BradWerth I added an edit to show that both config vars are set. Thanks – sheepdog Sep 27 '18 at 06:04

1 Answers1

2

Looking at the PostgreSQLAdapter source on github, I saw that a variables hash is checked for the presence of a "timezone" key. 

      variables = @config.fetch(:variables, {}).stringify_keys

      # If using Active Record's time zone support configure the connection to return
      # TIMESTAMP WITH ZONE types in UTC.
      unless variables["timezone"]
        if ActiveRecord::Base.default_timezone == :utc
          variables["timezone"] = "UTC"
        elsif @local_tz
          variables["timezone"] = @local_tz
        end
      end

The PostgreSQLAdapter docs show that :variables is an option in config/database.yml, so I just added variables: {timezone: 'US/Pacific'} to database.yml.

# database.yml
default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  variables: {timezone: 'US/Pacific'}

Kind of weird that you have to set it here instead of in config/, but it works.

sheepdog
  • 625
  • 5
  • 19