1

I have the problem, that doing a query in rails converts the the wrong time in the generated postgres sql statement. The time in the shell, ruby and postgres is 23:32, but in the generated sql statement it is 22:32

Any Ideas whats wrong? I Use rails 3.2.11, pg gem 0.14.1 (installed with bundle install)

debian system time:

$ date
Sat Feb  9 23:32:10 CET 2013

in ruby:

1.9.2p290 :004 > Time.now
=> 2013-02-09 23:32:15 +0100

in postgres:

=> select current_time;
   timetz       
--------------------
23:32:24.213487+01
(1 row)

generated rails query:

1.9.2p290 :003 > Item.where "next_crawling_at < ?", Time.now
Item Load (1.1ms)  SELECT "items".* FROM "items" WHERE (next_crawling_at < '2013-02-09 22:32:17.935595')
agreif
  • 411
  • 2
  • 15
  • [This related answer may be of help.](http://stackoverflow.com/questions/9571392/ignoring-timezones-altogether-in-rails-and-postgresql/9576170#9576170) – Erwin Brandstetter Feb 09 '13 at 23:08
  • Possible duplicate of http://stackoverflow.com/questions/9571392/ignoring-timezones-altogether-in-rails-and-postgresql/ – Chris Travers Apr 19 '13 at 02:29

1 Answers1

1

Looks like you have set UTC time zone in your client - even though that seems to contradict your result from Time.now. What do you get for Time.zone?

Without the additional DST-offset for summertime (since we have winter now), your time zone is probably CET (Central European Time), while Time.now seems to be translated to UTC.

You could force a particular time zone in Ruby with Time.now.in_time_zone("Europe/Paris") - (or whatever time zone name fits the time zone setting of your DB).

Or you can force UTC timestamps with Time.now.utc and append AT TIME ZONE 'Europe/Berlin' in Postgres.

Or you operate with timestamp with time zone everywhere.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • thanks for the explanation. Time.zone result: Loading production environment (Rails 3.2.11) 1.9.2p290 :001 > Time.zone => (GMT+00:00) UTC – agreif Feb 10 '13 at 07:33
  • @agreif: Explains it, doesn't it? – Erwin Brandstetter Feb 10 '13 at 07:44
  • Now the problem is, that im my rails app I can set datetime values myself, (where I could provide timezone info), but rails also automatically maintains the created_at/updated_at columns automatically, that is done in the rails framework so that I cannot influence it. Is there a way to set it globally in rails, so that I can simply use bla_time = Time.now; model.save! – agreif Feb 10 '13 at 07:59
  • @agreif: I'm an expert with Postgres, but only an amateur with Rails. – Erwin Brandstetter Feb 10 '13 at 08:03
  • 1
    I understand, thanks. I found a good article on the rails timezone stuff here: http://www.elabs.se/blog/36-working-with-time-zones-in-ruby-on-rails – agreif Feb 10 '13 at 08:10