Time Ranges are Clearer When Querying Databases

Active Record‘s query interface has many different ways to turn your logic into SQL to get rows from your database.

I'm co-chairing RailsConf 2024 in Detroit May 7–9.
Come and join us →

I previously extolled the virtues of the core extensions to Date and Time and in our web applications we‘re often extracting records from databases where time periods are one of our primary filters. So let’s use those extensions to query our database.

Instead of…

…using a text-based SQL fragments to query time fields:

User.where("created_at > ? AND created_at < ?", 2.weeks.ago, 1.week.ago).to_sql
#=> "SELECT ... WHERE (created_at > '2022-08-31 11:29:53.945995' AND created_at < '2022-09-07 11:29:53.946280')"
User.where("created_at > ? AND created_at < ?", 2.weeks.ago.beginning_of_day, 2.weeks.ago.end_of_day).to_sql
#=> "SELECT ... WHERE (created_at > '2022-08-31 00:00:00' AND created_at < '2022-08-31 23:59:59.999999')"
User.where("created_at >= ?", 2.weeks.ago.beginning_of_day).to_sql
#=> "SELECT ... WHERE \"users\".\"created_at\" >= '2022-08-31 00:00:00'"

Use…

…Ranges of time passed as an argument.

User.where(created_at: (2.weeks.ago..1.week.ago)).to_sql
#=> "SELECT ... WHERE \"users\".\"created_at\" BETWEEN '2022-08-31 11:29:33.248193' AND '2022-09-07 11:29:33.248938'"
User.where(created_at: 2.weeks.ago.all_day).to_sql
#=> "SELECT ... WHERE \"users\".\"created_at\" BETWEEN '2022-08-31 00:00:00' AND '2022-08-31 23:59:59.999999'"
User.where(created_at: (2.weeks.ago.beginning_of_day..)).to_sql
#=> "SELECT ... WHERE \"users\".\"created_at\" >= '2022-08-31 00:00:00'"

Why?

The ruby syntax in this case is shorter and clearer.

You’re also likely to make fewer mistakes as the SQL statement generated by the scope won‘t have a big splurge of your text inserted into it.

In addition the SQL generated by the framework when passed a range takes advantage of the correct BETWEEN SQL syntax (at least in Postgres). This more accurately describes the behaviour and allows the underlying database to use it’s statisics and indexes to return results as quickly as possible from the resulting query.

Why not?

Passing Ranges to Active Record scopes seems less common than it should be. I presume this is because String-based conditions are listed first in the guides documentation, and were—for early versions of Rails—the primary syntax.

There’s very little reason not to use this more modern syntax. It’s clearer to read and reason about when you’re using the features of Active Record than throwing Strings around. That way it stands out more when you do have to drop down to Strings for more complicated SQL queries.

My final example uses an endless Range which was introduced in Ruby 2.6 (in 2018) and it took a little while to include into Rails so you might see examples that interplate Strings to get the same effect.

Indexes!

If you’re regularly querying on a field it is probably worth investigating whether your database table could benefit from adding a database index.

Still running UK’s friendliest, Ruby event on Friday 28th June.
Ice cream + Ruby →