Posted almost 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:

Copy
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.

Details

The Ruby code above triggers the following query:

Copy
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:

Copy
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.

Solutions

One solution is to cast the datetime to a date in MySQL:

Copy
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:

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

Once an application no longer requires constant development, it needs periodic maintenance for stable and secure operation. makandra offers monthly maintenance contracts that let you focus on your business while we make sure the lights stay on.

Owner of this card:

Avatar
Henning Koch
Last edit:
6 months 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