Benito Serna Helping you be better with Ruby on Rails

Building a DateTime range to query for the records created a "week before"

Imagine that you need to query for the records created in the “week before” of a given date.

How would you do it?

Example

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.

Asking to ActiveRecord

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!

Building the range

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)

Try this code in the console

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.

Related articles