PGError: ERROR: unrecognized time zone name: "UTC"

Question

Is there anything wrong with this (User model, line 95):

scope :by_recent, where('created_at BETWEEN ? AND ?', 1.month.ago, Time.now).order('created_at desc').

Whenever I start rails s or rails c, I get:

 /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract_adapter.rb:202:in `rescue in log': PGError: ERROR:  unrecognized time zone name: "UTC" (ActiveRecord::StatementInvalid)

Commenting that line in my model, makes the error go away. Strange. I am using postgres 9 by the way. Installed via Homebrew (brew install posgtres).

This link, suggests the following below. But I don't understand what the person is suggesting as a solution...

MikZ: i've figured this out

rails are setting accessors with automatic timezone conversions when you override acessor (with table colum) you will get to serious problems

when leaving rails default (or setting timezone) you will get nice results but if you override accessors you will get to serious problems with that

so: don't ever override "time" attibutes' accessors

Full error:

/Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract_adapter.rb:202:in `rescue in log': PGError: ERROR:  unrecognized time zone name: "UTC" (ActiveRecord::StatementInvalid)
: SET time zone 'UTC'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract_adapter.rb:194:in `log'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/postgresql_adapter.rb:496:in `execute'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/postgresql_adapter.rb:961:in `configure_connection'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/postgresql_adapter.rb:939:in `connect'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/postgresql_adapter.rb:228:in `initialize'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/postgresql_adapter.rb:25:in `new'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/postgresql_adapter.rb:25:in `postgresql_connection'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:228:in `new_connection'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:236:in `checkout_new_connection'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:190:in `block (2 levels) in checkout'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:186:in `loop'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:186:in `block in checkout'
    from /Users/Chris/.rvm/rubies/ruby-1.9.2-p136/lib/ruby/1.9.1/monitor.rb:201:in `mon_synchronize'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:185:in `checkout'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:93:in `connection'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:316:in `retrieve_connection'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract/connection_specification.rb:97:in `retrieve_connection'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract/connection_specification.rb:89:in `connection'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/base.rb:1330:in `replace_bind_variables'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/base.rb:1317:in `sanitize_sql_array'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/base.rb:1224:in `sanitize_sql_for_conditions'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/relation/query_methods.rb:206:in `build_where'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/relation/query_methods.rb:77:in `where'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activerecord-3.0.3/lib/active_record/base.rb:441:in `where'
    from /Users/Chris/Sites/site_name/app/models/user.rb:95:in `<class:User>'
    from /Users/Chris/Sites/site_name/app/models/user.rb:1:in `<top (required)>'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:454:in `load'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:454:in `block in load_file'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:596:in `new_constants_in'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:453:in `load_file'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:340:in `require_or_load'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:491:in `load_missing_constant'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:183:in `block in const_missing'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:181:in `each'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:181:in `const_missing'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/inflector/methods.rb:124:in `block in constantize'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/inflector/methods.rb:123:in `each'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/inflector/methods.rb:123:in `constantize'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:528:in `block in <class:Reference>'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:538:in `yield'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:538:in `default'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:538:in `get'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/bundler/gems/devise-bbaaefa995c3/lib/devise/mapping.rb:76:in `to'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/bundler/gems/devise-bbaaefa995c3/lib/devise/rails/routes.rb:164:in `block in devise_for'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/bundler/gems/devise-bbaaefa995c3/lib/devise/rails/routes.rb:160:in `each'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/bundler/gems/devise-bbaaefa995c3/lib/devise/rails/routes.rb:160:in `devise_for'
    from /Users/Chris/Sites/site_name/config/routes.rb:75:in `block in <top (required)>'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/actionpack-3.0.3/lib/action_dispatch/routing/route_set.rb:233:in `instance_exec'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/actionpack-3.0.3/lib/action_dispatch/routing/route_set.rb:233:in `draw'
    from /Users/Chris/Sites/site_name/config/routes.rb:1:in `<top (required)>'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:235:in `load'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:235:in `block in load'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:225:in `block in load_dependency'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:596:in `new_constants_in'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:225:in `load_dependency'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:235:in `load'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/application.rb:127:in `block in reload_routes!'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/application.rb:127:in `each'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/application.rb:127:in `reload_routes!'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/application.rb:120:in `block in routes_reloader'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/file_update_checker.rb:32:in `call'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/file_update_checker.rb:32:in `execute_if_updated'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/application/finisher.rb:51:in `block (2 levels) in <module:Finisher>'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/application/finisher.rb:52:in `call'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/application/finisher.rb:52:in `block in <module:Finisher>'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/initializable.rb:25:in `instance_exec'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/initializable.rb:25:in `run'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/initializable.rb:50:in `block in run_initializers'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/initializable.rb:49:in `each'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/initializable.rb:49:in `run_initializers'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/application.rb:134:in `initialize!'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/application.rb:77:in `method_missing'
    from /Users/Chris/Sites/site_name/config/environment.rb:5:in `<top (required)>'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:239:in `require'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:239:in `block in require'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:225:in `block in load_dependency'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:596:in `new_constants_in'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:225:in `load_dependency'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/activesupport-3.0.3/lib/active_support/dependencies.rb:239:in `require'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/application.rb:103:in `require_environment!'
    from /Users/Chris/.rvm/gems/ruby-1.9.2-p136/gems/railties-3.0.3/lib/rails/commands.rb:22:in `<top (required)>'
    from script/rails:6:in `require'
    from script/rails:6:in `<main>'

EDIT:

Actually, the problem is related to this issue. I've also moved from macports to homebrew. Do I need to remove /opt/local before installing postgres with homebrew?

Second, I can do rake db:drop:all and rake db:create, but not rake db:migrate. I get:

rake aborted!
PGError: ERROR:  unrecognized time zone name: "UTC"
: SET time zone 'UTC'

Answer 1

Yeap, it may have seemed that there is something seriously broken with the database. I remembered installing PostgreSql via homebrew, but possibly not stopping the macport installation of the postgres server, that was still running in the background.

Through the assistance of another developer (RhodiumToad), I was able to fix this by doing the following steps:

1. stop the server
2. re-run initdb
3. start the server again

Earlier, I removed /usr/local/var/postgres, via rm -rf /usr/local/var/postgres. So this is what I needed to do:

ps axw

find the postgres server

117   ??  S      0:00.23 /usr/local/Cellar/postgresql/9.0.3/bin/postgres -D /usr/local/var/postgres -r /usr/local/var/postgres/server.

kill it, which should also kills any other postgres process' running in the bg

kill 117

The above killed the process and also stopped the server. Now I had to initialize the database and everything seems to work correctly again:

initdb /usr/local/var/postgres

Hope this helps someone one who comes across the same issue.