Posted about 10 years ago. Visible to the public. Repeats.

Don't compare datetimes with date ranges in MySQL and PostgreSQL

When selecting records in a date range, take care not to do it like this:

start_date = Date.parse('2007-05-01') end_date = Date.parse('2007-05-31') LogItem.where(:created_at => start_date .. end_date)

The problem is that created_at is a datetime (or Time in Ruby), while start_date and end_date are simple dates. In order to make sense of your query, your database will cast your dates to datetimes where the time component is 00:00:00. Because of this the query above will lose records created from 2007-05-31 00:00:01 to 2007-05-31 23:59:59.


The Ruby code above triggers the following query:

SELECT * FROM log_items WHERE created_at BETWEEN '2007-05-01' AND '2007-05-31'

Your database sees that you are trying to compare a datetime with a date and silently casts your query to:

SELECT * FROM log_items WHERE created_at BETWEEN '2007-05-01 00:00:00' AND '2007-05-31 00:00:00'

Note how you just lost all records from 2007-05-31 00:00:01 to 2007-05-31 23:59:59.


One solution is to cast the datetime to a date before you make the query:

LogItem.where('DATE(log_items.created_at) BETWEEN ? AND ?', start_date, end_date)

This may cause an index on the column to no longer be used.

Another solution is to be more explicit about what you want to select:

LogItem.where(:created_at => start_date.beginning_of_day..end_date.end_of_day)

Your development team has a full backlog of feature requests, chores and refactoring coupled with deadlines? We are familiar with that. With our "DevOps as a Service" offering, we support developer teams with infrastructure and operations expertise.

Owner of this card:

Henning Koch
Last edit:
about 1 month ago
by Henning Koch
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Henning Koch to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more