Posted over 7 years ago. Visible to the public. Repeats.

Don't compare datetimes with date ranges in MySQL

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, MySQL 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'

MySQL 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 in MySQL:

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

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)

makandra has been working exclusively with Ruby on Rails since 2007. Our laser focus on a single technology has made us a leader in this space.

Owner of this card:

Henning Koch
Last edit:
over 1 year ago
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 cookies to improve usability and analyze traffic.
Accept or learn more