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

Question

Something is 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 s rails or c rails, 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 out this line in my model, the error goes away. It’s strange. By the way, I use postgres 9. Installed via Homebrew (brew install posgtres).

This link suggests the following below. But I do not understand what a person offers as a solution ...

MikZ: I figured it out

the rails set the time zone to change automatically, when you redefine the user (with the colum table), you will get into a serious problem

when you exit the rails by default (or setting the time zone), you will get good results, but if you override the accessories, you get serious problems with this

like this: never redefine attibutes 'accessors' time

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 problem. I also moved from macports to homebrew. Do I need to remove / opt / local before installing postgres with homebrew?

Secondly, 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 might seem that something is seriously broken with the database. I remember installing PostgreSql through homebrew, but may not have stopped installing the macport postgres server, which was still running in the background.

With the help of another developer (RhodiumToad), I was able to fix this by following these steps:

• stop server
• restart initdb
• start the server again.

I used to delete / usr / local / var / postgres, via rm -rf / usr / local / var / postgres. So here is what I need to do:

 ps axw 

find 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 kill any other postgres process running in bg

 kill 117 

The above process killed and 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 who is facing the same problem.

Answer 2

My problem was that I had multiple instances of the PostgreSQL server. Kill every process and restart only the one I need. I don't know why this caused PGError: ERROR: unrecognized time zone name: "UTC" : SET time zone 'UTC'

  ps axw | grep postgres 

Then kill , as the Christian said above.

NOTE. I do not need to run initdb again.

Answer 3

not 100% sure, but the method of defining the area makes a string of Time parameters that you pass, which may cause some problems. try using symbols instead and see if this works:

 scope { :conditions => {:created_at => 1.month.ago..Time.now}, :order => 'model.created_at DESC' } 

Answer 4

Well, I assume that something in this line in your model is trying to force the time zone used when talking to the database. I think it makes some sense ...

In any case, considering that the SET time zone 'UTC' is a command sent by postgresql when connecting (which seems to be the case when looking at this other side), I would expect it to work. Of course, this works on my (Linux) postgresql installation. Can you check if it will work if you connect to the database using psql and manually issue a command?

This seems to indicate a broken postgresql installation. Removing things and reinstalling them would be a smart move. My own postgresql installation uses the time zone data from the operating system, i.e. it searches in /usr/share/zoneinfo . Perhaps your installation is trying to collect data from some place that was present during the build, but has been deleted since you switched. I believe that he cannot find the location with the data during setup, he will install his own nested copy.

Answer 5

This error occurs because Postgres has been updated but not restarted. The old version worked on a missing timezone file.

Just rebooted as said here: fooobar.com/questions/114584 / ...