Benito Serna Tips and tools for Ruby on Rails developers

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

May 6, 2021

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

Subscribe to get future posts via email

Here I try 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 posts.