Rails 3 DateTime and Time beginning_of_day end_of_day format incorrect

Question

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!

Answer 1

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

Answer 2

You are probably using UTC time in your database. This is a good thing but it leads to all sorts of confusion. The -0700 offset is converted to 07:00:00 since that's when your day starts in UTC time.

Answer 3

You could use the PostgreSQL AT TIME ZONE construct with UTC timestamps:

SELECT e.*
FROM   events e
WHERE  starttime >= '2012-04-09 00:00' AT TIME ZONE 'UTC'
AND    starttime <  '2012-04-10 00:00' AT TIME ZONE 'UTC'

I recently explained PostgreSQL timestamp and time zone handling in a related answer.