Working with date ranges in Active Record

09 Mar 2016

TL;DR: Use the by_star gem and save yourself a lot of hassle.

When you're trying to get records for a particular date range in Active Record, for example from the beginning of the month to the end of the month, you may be tempted to write code like this:

beginning_of_month = Date.today.beginning_of_month
end_of_month = beginning_of_month.end_of_month
Post.where(created_at: beginning_of_month..end_of_month)

Let's say that the month is March 2016 (and it is, at the time of writing). This query will get you all posts between the 1st of March and the 31st of March, but not in the way you expect it. It will get you all posts between 00:00:00 of the 1st of March and 00:00:00 of 31st of March, which is probably not what you want, as it misses the last days posts completely.

What you'll want is all the posts between 00:00:00 of the 1st of March and 23:59:59.99999 of the 31st of March.

To do this, you could use Time.current instead:

beginning_of_month = Time.current.beginning_of_month
end_of_month = beginning_of_month.end_of_month
Post.where(created_at: beginning_of_month..end_of_month)

This will give you the right set of posts, because Time.current.end_of_month is something like Thu, 31 Mar 2016 23:59:59 UTC +00:00 (or it might even be in your local time zone, like `Thu, 31 Mar 2016 23:59:59 AEDT +11:00).

Or you could even do this:

beginning_of_month = Date.today.beginning_of_month
beginning_of_next_month = beginning_of_month.next_month
Post.where(created_at: beginning_of_month..beginning_of_next_month)

This will also give you the right set of posts, because beginning_of_month.next_month is something like Fri, 1 Apr 2016 00:00:00 UTC +00:00 (or it might even be in your local time zone, like `Thu, 31 Mar 2016 23:59:59 AEDT +11:00).

Or you could use the by_star gem. I wrote this gem to easily query date / time ranges in Active Record and it has saved me a lot of frustration. The above code examples would then become:

Post.by_month(Date.today)

This will retrieve all posts by the current month, and that means all the posts between 00:00:00 of the 1st of March and 23:59:59.99999 of the 31st of March will be returned if I ran this query today.

Conclusion

Use the by_star gem and save yourself a lot of hassle.

blog comments powered by Disqus