Querying a datetime range while taking into account the time zone

I'm having a lot of trouble with a query. I don't really know how to explain this well, but I'm going to try.

Basically, we have several objects with a 'posted_at' field that keeps the date and time something was posted, with the time 开发者_运维技巧zone, in a datetime field. I need to query and get a range by date with those objects.

Previously, I was converting that to Date and comparing it to another Date object. The query was something like this:

Date(posted_at) >= :start_date AND Date(posted_at) <= :end_date

However, when Postgre converted it to Date, it lost the timezone info which caused innacurate results to the query.

So, I changed to this:

if start_date then
    start_time = Time.zone.parse("#{start_date.year}-#{start_date.month}-#{start_date.day}")
    conditions << "posted_at >= :start"
    hash[:start] = start_time
end

if end_date then
    end_time = Time.zone.parse("#{end_date.year}-#{end_date.month}-#{end_date.day}").end_of_day
    conditions << "posted_at <= :end"
    hash[:end] = end_time
end

While this gets me the accurate results, it also has horrible performance and is causing some timeouts in my application.

I couldn't find any other way to do this query and still keep the accurate results. Would anyone have some advice or ideas?

Thank you in advance.

You never want to store timezone information in your database.

Here's a read that discusses some of the pitfalls:

http://derickrethans.nl/storing-date-time-in-database.html

You'll get better results as tadman suggests: add a new field with your timestamp at time zone 'utc', and index it. You'll then be able to grab stuff using posted_at between ? and ?.

You may have more luck converting your start and end times to UTC which would render the time-zone mostly irrelevant when making the query itself. This is done easily enough:

start_date.to_time.to_datetime.beginning_of_day.utc
end_date.to_time.to_datetime.end_of_day.utc

You can also adjust your query to be:

posted_at BETWEEN :start AND :end

Be sure to have an index on the fields you're searching, too, or you will get horrible performance.