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.