How to Query Active Record for Weekdays Only

Using SQL Functions in Active Record

Most SQL databases provide functions to extract parts of a date. For instance, PostgreSQL uses EXTRACT(DOW FROM date), where DOW stands for “day of the week,” returning values from 0 (Sunday) to 6 (Saturday).

Let’s consider a Task model with a created_at column. Our goal is to fetch records that were created on weekdays only. We’ll use Active Record’s querying capabilities to achieve this.

# app/models/task.rb
class Task < ApplicationRecord
  scope :weekdays_only, -> { where("EXTRACT(DOW FROM created_at) BETWEEN 1 AND 5") }
end

Explanation:

• EXTRACT(DOW FROM created_at): Extracts the day of the week from the created_at timestamp.
• BETWEEN 1 AND 5: Limits the selection to Monday through Friday.

Using Ruby’s Date Methods with Active Record

If you want to strictly use Ruby’s Date methods, you can modify the code to filter records after fetching them from the database, but this is not efficient for larger datasets.

# app/models/task.rb
class Task < ApplicationRecord
  def self.weekdays_only_ruby
    all.select { |task| task.created_at.on_weekday? }
  end
end

Explanation:

• This method first fetches all records from the database and then filters them using Ruby’s on_weekday? method.
• task.created_at.on_weekday?: This converts the created_at timestamp to a Ruby Date object and checks if it’s a weekday (Monday through Friday).

Example Usage:

Task.weekdays_only_ruby

Note: This approach can be inefficient because it fetches all records from the database first, then processes them in memory. It’s recommended for small datasets. If you’re working with larger datasets, the SQL solution will be much more efficient.

Combining Conditions with Active Record

You might want to filter records by weekdays and a specific condition, like a status. This is possible by chaining scopes:

# app/models/task.rb
class Task < ApplicationRecord
  scope :weekdays_only, -> { where.not("EXTRACT(DOW FROM created_at) IN (0, 6)") }
  scope :completed, -> { where(status: 'completed') }
end

Example Usage:

Task.weekdays_only.completed

Querying Across Different Time Zones

When dealing with time zones, ensure the created_at timestamps are converted appropriately. Use the AT TIME ZONE clause in SQL for accurate comparisons.

# app/models/task.rb
class Task < ApplicationRecord
  scope :weekdays_only_in_timezone, ->(timezone) {
    where("EXTRACT(DOW FROM created_at AT TIME ZONE ?) BETWEEN 1 AND 5", timezone)
  }
end

Example Usage:

Task.weekdays_only_in_timezone('UTC')

Handling Other Databases

Different SQL databases may use different functions for date extraction:

• MySQL: DAYOFWEEK(created_at)
• SQLite: strftime('%w', created_at)

Make sure to adjust your queries according to the database being used:

# Example for MySQL
scope :weekdays_only, -> { where("DAYOFWEEK(created_at) BETWEEN 2 AND 6") }

After implementing these scopes, querying for Task.weekdays_only would result in a collection of tasks that are only created on weekdays. Here’s an example:

tasks = Task.weekdays_only
tasks.each do |task|
  puts task.created_at
end

Output:

2024-08-26 10:30:00 UTC  # Monday
2024-08-27 11:45:00 UTC  # Tuesday
2024-08-28 09:00:00 UTC  # Wednesday

Conclusion

Filtering Active Record results to include only weekdays is straightforward by leveraging SQL functions and Ruby methods to extract parts of a date.

Other articles you may enjoy:

• How to Extract Total Days Hours or Minutes from PostgreSQL
• How to Create Charts Using Ruby on Rails