如何用Rails+PostgreSQL查询当前时间是否在门店营业时间内？

Got it, let's work through this problem step by step. The tricky part here is handling overnight business hours (like your example where the shop opens at 4 AM and closes at 1 AM the next day)—a simple BETWEEN check won't work here because close_at is earlier than open_at. We need to split the logic into two scenarios.

Core Logic Breakdown #

We need to handle two types of business hour ranges:

• Non-overnight ranges (open_at <= close_at): The business operates within a single day, so we just check if the current time falls between open_at and close_at.
• Overnight ranges (open_at > close_at): The business crosses midnight, so the valid time window includes:
  1. From open_at to midnight on the current day
  2. From midnight to close_at on the next day
     We check if the current time is in either of these windows.

Rails Query Implementation #

You can use ActiveRecord's where method with a SQL snippet to build the query:

# Fetch Timing records where the current time falls within business hours
active_timings = Timing.where(
  "
    -- Case 1: Non-overnight hours (same day)
    (open_at <= close_at AND CURRENT_TIME BETWEEN open_at AND close_at)
    OR
    -- Case 2: Overnight hours (crosses midnight)
    (open_at > close_at AND (CURRENT_TIME >= open_at OR CURRENT_TIME <= close_at))
  "
)

Verify with Your Example #

Let's test this against your specific data:

• Current UTC time: Tue, 20 Mar 2018 06:46:28 UTC (time portion: 06:46:28)
• Your Timing record: open_at: 04:00:00, close_at: 01:00:00 (this is an overnight range)
• The query checks if 06:46:28 >= 04:00:00 (which is true), so this record will be included—meaning the shop is currently open.

Additional Tips #

• Time Zone Consistency: Make sure your Rails app's time zone (config.time_zone) matches your PostgreSQL database's time zone. If needed, explicitly specify the time zone in the query (e.g., CURRENT_TIME AT TIME ZONE 'UTC') to avoid mismatches.
• Check a Single Record: If you just need to verify one specific Timing entry, use exists?:

  timing = Timing.find(3)
  is_currently_open = timing.where(
    "
      (open_at <= close_at AND CURRENT_TIME BETWEEN open_at AND close_at)
      OR
      (open_at > close_at AND (CURRENT_TIME >= open_at OR CURRENT_TIME <= close_at))
    "
  ).exists?
  

内容的提问来源于stack exchange，提问作者Vishal