Error when pushing data to Heroku: time zone displacement out of range

Asked 16/11, 2011 at 12:19 Answered 8/8, 2013 at 13:53

I run the following command to push the contents of my local database to Heroku:

heroku db:push --app my-app

From my home computer this works flawlessly but from my work computer I get this error:

Taps Server Error: PGError: ERROR: time zone displacement out of range: "2011-11-15 12:00:00.000000+5894114400"

I'm not sure where that date is coming from, I can't find it in the data anywhere. Any ideas what's going on and/or how to fix it?

Negotiate answered 16/11, 2011 at 12:19 Comment(3)

I've seen a suggestion to use 1.9.2-p290 instead of 1.9.3-p0 (if you are using it) here But it does not helped me. I'm experiencing the same issue with the both versions. – Tetratomic 17/11, 2011 at 21:43

@ie it does work for me (that was me making the suggestion actually). – Negotiate 21/11, 2011 at 9:31

This post suggests using pgbackups to move your data instead. – Bjorn 15/8, 2012 at 2:22

Using Ruby 1.9.2-p290 instead of 1.9.3-p0 fixed it for me. According to Roger Braun, this is the reason:

The problem is (I think), that marshalling changed between Ruby 1.9.2 and 1.9.3, so this is not really a taps error. Just use whatever version heroku runs to push and pull databases (Probably 1.9.2).

Negotiate answered 12/12, 2011 at 13:9 Comment(4)

Here's a link to the issue in the taps github repository – Tenderhearted 12/4, 2012 at 14:30

I encountered this with 1.9.3p125 on my local machine and 1.9.3p125 on my heroku instance -- though I can't be certain that taps is actually running my instance. – Tenderhearted 12/4, 2012 at 14:32

taps doesn't run on your instance - it has it's own instances so your ruby version won't apply to it. – Car 2/5, 2012 at 4:21

I strongly encourage everyone to use heroku's pgbackups(devcenter.heroku.com/articles/pgbackups) solution as described by Aditya below – Empanel 8/8, 2012 at 2:51

Downgrading to Ruby 1.9.2 from Ruby 1.9.3 does not sound like an appealing option to me.

Heroku's devcentre page actually suggests using pgbackups addon to managing databases ( https://devcenter.heroku.com/articles/pgbackups) . It may sound like it's only for taking backups of production databases down to your local machine but if you read it carefully, they have a large section which deals with "importing a database". What they suggest is that you upload your database to a publically accessible location and run the suggested command

heroku pgbackups:restore DATABASE 'http://s3.amazonaws.com/.....mydb.dump?authparameters'

So, in effect they provide commands to dump your local database, suggest ways to upload it to a location that heroku's servers can fetch your database dump from (assuming your local development machine is not publically accessible from the internet) and then the above command for it to be uploaded into production environment on heroku.

Just putting this up here, since i think problem has been persisting for a very long time without adequate resolution on the real problem.

Akeylah answered 9/6, 2012 at 22:18 Comment(2)

This worked for me. I didn't have to modify my version of ruby or hack the database in anyway. Thanks, Aditya! – Empanel 8/8, 2012 at 2:49

This works perfectly fine for the ones who also use a postgresql database on their local dev environments. – Catbird 3/5, 2013 at 19:2

I run 1.9.3p125 on Heroku and 1.9.3p125 on my local machine. If I want to db:push I just switch to 1.9.2 while uploading:

rvm use ruby-1.9.2-p290
heroku db:push --app my-app
rvm use ruby-1.9.3-p125

Then once the push is complete, I switch back to 1.9.3.

Tenderhearted answered 12/4, 2012 at 17:55 Comment(0)

It didn't work for me either using 1.9.3 or 1.9.2.

As Pablo pointed out the problem has to do with the marshalling of the dates (the marshalling converts the date to 12:00:00+XXXX" despite my date type was 'timestamp without timezone').

Anyway, I worked it around by setting the dates to null, so I could successfully push to Heroku. Very poor solution, though. I hope the bug will get fixed soon.

Spindle answered 30/4, 2012 at 13:37 Comment(3)

I've tried both 1.9.2 and 1.9.3 - neither was the problem. Remove the tool belt - problem solved! Discussion of removing toolbelt here: johntwang.com/blog/2011/09/13/remove-heroku-toolkit then reinstall heroku gem ! – Grim 28/1, 2013 at 14:6

@Diego Pino: How did you set your dates to null? – Pointer 8/8, 2013 at 13:33

@Pointer I don't clearly remember but most likely was opening a PostgreSQL session and setting the date to null using SQL. The push to Heroku. – Spindle 8/8, 2013 at 16:33

On my platform of choice (gentoo linux) Ruby 1.9.2 is not available anymore. Anyway a version of ruby 1.8.x can be installed at the same time.

Here is how I worked around the issue:

$ eselect ruby list
Available Ruby profiles:
[1]   ruby18 (with Rubygems)
[2]   ruby19 (with Rubygems) *

$ sudo eselect ruby set 1
Password: 
Successfully switched to profile:
ruby18

Now I had to temporally comment from my project Gemfile all the gems that don't work with ruby 1.8 (for example paperclip)

$ bundle install
& bundle exec heroku db:push

Uncomment from Gemfile what was commented before

$ sudo eselect ruby set 2
Password: 
Successfully switched to profile:
ruby19

Not a clean solution but at least works fine.

Placida answered 6/9, 2012 at 9:37 Comment(0)

Just changing the ruby version has not worked for me. Instead I've narrowed down the issue to the new Heroku Toolbelt.

When using the older heroku gem I'm able to db:push just fine. Until heroku fixes it i've created a new gemset and installed the heroku gem. I just switch to this gemset whenever I need to do a db:push.

Chard answered 8/11, 2012 at 23:0 Comment(1)

What 'old' version of the Heroku gem are you using? I've tried various different versions, but I still get the same issue. – Repetend 10/11, 2012 at 20:54

Which Heroku stack are you on? If you are on bamboo-ree-1.8.7 the right version to use is 1.8.7. This is a marshalling problem and can be solved by using the same Ruby version on both the server and the client.

Reginareginald answered 1/5, 2012 at 19:30 Comment(0)

Try to use native taps without heroku db:push.

taps server POSTGRES_DATABASE_REMOTE_URL login pass

taps push sqlite://db/development.sqlite3 login:pass@localhost:5000

solved the problem for me

Unquestionable answered 29/12, 2012 at 7:41 Comment(0)

It helped me to solve my problem, very practical https://github.com/ricardochimal/taps/issues/92#issuecomment-11996909

Edyth answered 29/1, 2013 at 10:15 Comment(0)

On win7-x64, creating app in heroku's cedar and setting pik (rvm-alternative) to use ruby 1.9.2 worked. In a nutshell, what I did:

created a new heroku app in cedar stack (running ruby-1.9.2)
```
heroku create -s cedar
```
installed pik (rvm-alternative), then followed the post-install instructions
```
gem install pik
```
installed ruby-1.9.2p290, added <RUBY192_INSTALL_DIR>/bin to $env:PATH
installed DevKit to ruby-1.9.2
ensured all necessary gems are installed in both ruby versions, 1.9.3 and 1.9.2
```
pik gem install <gem-1> <gem-2> ... <gem-n>
```

specified db gems for production, development, and test environments in Gemfile

# Development + Test:
group :development, :test do
  gem 'pg', :platforms => :mingw
end

# Heroku:
group :production do
  gem 'thin'
  gem 'pg'
end

removed platform refs to mingw32 in Gemfile.lock after
```
bundle install
```

added new edits in Gemfile and Gemfile.lock (generated) to repo

git add .
git commit -am "rebuilt Gemfile for Heroku"
git push heroku master

raked up data models, pushed local data up

heroku run rake db:migrate
heroku db:push
heroku open

then switched back to ruby-1.9.3
```
pik use 193
```

Chloramine answered 7/6, 2012 at 10:39 Comment(0)

Finally got this to work with help from Dosha's answer here. (And thank you to hernanvicente above for the tip.)

Make sure your version of ruby matches the version running on Heroku. It seems like 1.9.2 is the stablest version for these migrations.

Change your gemfile to have the following (assuming you're using SQLite):

group :development do
 gem 'taps', :require => false
 gem 'sqlite3'
end

This likely still does not resolve your problem because your heroku db:push command is using the Heroku toolbar instead of the older, now-deprecated heroku gem. Unfortunately, we actually want the older gem, but the Heroku Toolbar is being called by heroku. To get around this, you will need to install the heroku gem on your version of ruby 1.9.2 and then access it by its specific filepath.

So, the next steps show how you can get this to work:

Run the following commands into your console:

rvm install ruby-1.9.2-p320
rvm use ruby-1.9.2-p320
bundle install`
sudo gem install heroku --no-ri --no-rdoc

Then run:

rake assets:clean
bundle exec rake assets:precompile

Commit your changes to Github.

Then enter the following into your console:

~/.rvm/gems/ruby-1.9.2-p320/gems/heroku-2.40.0/bin/heroku db:push (Use your own filepath, if it's different from this.)

Pointer answered 8/8, 2013 at 13:53 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags