Rails 3 DateTime and Time beginning_of_day end_of_day format incorrect
postgresql, ruby-on-rails, ruby-on-rails-3
Solution
See: http://railscasts.com/episodes/106-time-zones-in-rails-2-1
I think the times you're quoting above are actually the same time, just one is UTC time (you're on pacific time right?). To fix this you could try setting:
config.time_zone = "Pacific Time (US & Canada)"
in your environment.rb
You could also try:
DateTime.now.utc.beginning_of_day
DateTime.now.utc.end_of_day
so the you're using UTC as per the DB
Problem
I am trying to find events on certain days with this code: ``` Event.where('starttime BETWEEN ? AND ?', DateTime.now.beginning_of_day, DateTime.now.end_of_day) ``` In rails console if I run `DateTime.now.beginning_of_day` I get exactly what I expect: Mon, 09 Apr 2012 00:00:00 -0700 I can see where the problem is occurring but looking at the SQL in rails console. Somehow when the date makes it into the SQL query it gets automatically formatted to the wrong date and time. ``` SELECT "events".* FROM "events" WHERE (starttime BETWEEN '2012-04-09 07:00:00' AND '2012-04-10 06:59:59') ``` This is giving me results that vary from today until tomorrow, as the sql above says. I can see that when the DateTime.now.beginning_of_day also DateTime.now.end_of_day are being formatted incorrectly once they make it into the sql query. Do I need to be formatting this in a certain way? Any idea why it would go to 7:00 of today and 7:00 of tomorrow? I don't know if it makes a difference but I'm using PostgreSQL. Thanks!