Imagine that you need to query for the records created in the “week before” of a given date.
How would you do it?
For this example a “week before” means that…
If the given date is the Friday 2021-04-30 it will return the records created between the begining of the previous Friday 2021-04-23 and the last second of the Thursday 2021-04-29.
In rails, with ActiveRecord, you can ask for the records created between a range with…
Record.where(created_at: range)
This will build a BETWEEN query with the range you have provided, but how do you build the range?…
Let’s see!
Let’s start with the Friday 2021-04-30 from the example…
date = Date.new(2021, 4, 30)
# => Fri, 30 Apr 2021
If we substract 7 to it, it will return the previous Friday…
date - 7
# => Fri, 23 Apr 2021
It will do the same if we substract 7.days
date - 7.days
# => Fri, 23 Apr 2021
What happen if we substract 7.days.ago?…
It will return an error because 7.days.ago is not a number…
date - 7.days.ago
# Traceback (most recent call last):
# 1: from (irb):4
# TypeError (expected numeric)
It is actually a Datetime…
7.days.ago
# => Fri, 23 Apr 2021 10:22:54 CDT -05:00
We can convert it to a date with to_date…
7.days.ago.to_date
# => Fri, 23 Apr 2021
But let’s return to the range…
Now to get the Thursday 2021-04-29 we need to substract 1.day to our date
date - 1.day
# => Thu, 29 Apr 2021
So, to build the desired range we can do…
range = (date - 7.days)..(date - 1.day)
# => Fri, 23 Apr 2021..Thu, 29 Apr 2021
Or (without days and day)…
range = (date - 7)..(date - 1)
# => Fri, 23 Apr 2021..Thu, 29 Apr 2021
That will return a range of Date objects…
range.to_a
# => [Fri, 23 Apr 2021, Sat, 24 Apr 2021, Sun, 25 Apr 2021, Mon, 26 Apr 2021, Tue, 27 Apr 2021, Wed, 28 Apr 2021, Thu, 29 Apr 2021, Fri, 30 Apr 2021]
But normally our datetime fields store the information for utc. So, we should not query directly with the dates, because it
won’t match the expected timezone.
We need to build the range aware of the time zone that we need and then let rails transform the range to utc.
To do that we can use the rails methods beginning_of_day and end_of_day to convert our date in to a datetime aware of the time zone.
range = ((date - 7).beginning_of_day)..((date - 1.day).end_of_day)
# => Fri, 23 Apr 2021 00:00:00 CDT -05:00..Fri, 30 Apr 2021 23:59:59 CDT -05:00
And now that we have the range that we want we can pass it to the rails where method…
Record.where(created_at: range)
To understand this code better, try to run each example in the rails console and to test the things that maybe are not so clear at this moment.
I send an email each week, trying to share knowledge and fixes to common problems and struggles for ruby on rails developers, like How to fetch the latest-N-of-each record or How to test that an specific mail was sent or a Capybara cheatsheet. You can see more examples on Most recent posts or All post by topic.