The following Active Record query:

User.where(created_at: 1.day.ago...Time.current).to_sql

will generate this SQL:

SELECT
  "users".*
FROM
  "users"
WHERE
  "users"."created_at" >= '2023-06-21 06:38:26.330063'
  AND 
  "users"."created_at" < '2023-06-22 06:38:26.330184'

While the following Active Record query:

User.where(created_at: Date.current.all_day).to_sql

will generate the following SQL:

SELECT
  "users".*
FROM
  "users"
WHERE
  "users"."created_at" BETWEEN '2023-06-21 21:00:00' AND '2023-06-22 20:59:59.999999'

I noticed this difference while having a discussion with Cosmin Stamate and Jakob Cosoroabă on a Discord group about data ranges.

Thus I started asking myself:

• Why is there a difference?

• Why use BETWEEN in the second example or why not use it in the first example?

And had a hint that it must be related to ranges and the inclusion/exclusion of their ends.

To answer these questions, we need to explore the following concepts:

1. What is BETWEEN doing in PostgreSQL?

2. What does .all_day do?

3. What is the difference between ... and .. ?

PostgreSQL BETWEEN

This is straightforward. According to the PostgreSQL documentation on function comparisons, BETWEEN includes its endpoints:

What does all_day do?

.all_day seems to be defined in ActiveSupport:

# Source: https://github.com/rails/rails/blob/main/activesupport/lib/active_support/core_ext/date_and_time/calculations.rb#L310
def all_day
  beginning_of_day..end_of_day
end

So it is a range literal that includes its end value.

What is the difference between ... and .. ?

Here is what Ruby 3.2 documentation defines Range Literals:

A range has a starting point and an endpoint. Different from the math notation of using (.) to signify that the endpoints are not included and [.] to signify that the endpoints are included, in Ruby both .. and ... include the start point.

The difference between them is with regards to the endpoint:

• .. includes its ending value

• ... does NOT include its ending value

(1..2).to_a # => [1, 2]
(1...2).to_a # => [1]

They even have a method to tell you that:

(1..2).exclude_end? # => false
(1...2).exclude_end? # => true

So, why is the difference in SQL for .all_day vs 1.day.ago ...Time.current

The difference is that all_day uses an inclusive end range and 1.day.ago ... Time.current is an exclusive end range.

(1.day.ago...Time.current).exclusive_end? # => true
Date.current.all_day.exclusive_end? # => false

Thus in the case when using an inclusive end range, Active Record will generate an SQL statement with BETWEEN and when using an exclusive end range it will use the normal comparison >= AND < SQL statement.

Let's make the calls explicit. Here is the SQL query when using the exclusive end range:

User.
  where(created_at: 1.day.ago.beginning_of_day ... 1.day.ago.end_of_day).
  to_sql

# will generate the following statement
# please keep in mind this is executed with 
# Europe/Bucharest set as time zone

SELECT "users".* FROM "users" 
    WHERE 
    "users"."created_at" >= '2023-06-21 21:00:00' 
    AND 
    "users"."created_at" < '2023-06-22 20:59:59.999999'

Here is the query using inclusive end range:

User.
  where(created_at: 1.day.ago.beginning_of_day .. 1.day.ago.end_of_day).
  to_sql

# will generate the following statement
# please keep in mind this is executed with 
# Europe/Bucharest set as time zone

SELECT "users".* FROM "users" 
    WHERE 
    "users"."created_at" BETWEEN '2023-06-21 21:00:00' AND '2023-06-22 20:59:59.999999'

What about using AREL?

It will do the same thing:

users = User.arel_table
users.
  project(Arel.star).
  where(
    users[:created_at].between(1.day.ago.beginning_of_day ... 1.day.ago.end_of_day)
  ).to_sql

# will print

SELECT "users".* FROM "users" 
    WHERE 
    "users"."created_at" >= '2023-06-21 21:00:00' 
    AND 
    "users"."created_at" < '2023-06-22 20:59:59.999999'

# while using inclusive end range

users.
  project(Arel.star).
  where(
    users[:created_at].between(1.day.ago.beginning_of_day .. 1.day.ago.end_of_day)
  ).to_sql

# will print

SELECT "users".* FROM "users" 
    WHERE 
    "users"."created_at" BETWEEN '2023-06-21 21:00:00' AND '2023-06-22 20:59:59.999999'

---

Enjoyed this article?

Join my Short Ruby News newsletter for weekly Ruby updates. Also, check out my co-authored book, LintingRuby, for insights on automated code checks. For more Ruby learning resources, visit rubyandrails.info.