Rails 5基于PostgreSQL查询9:00至次日8:30时段降雨数据

嘿，我看到你在处理跨天的降雨统计时段时遇到了麻烦——这个场景确实有点 tricky，因为你的统计周期是当日9:00到次日8:30，不是常规的自然日，直接用BETWEEN肯定行不通，原因很简单：PostgreSQL里的时间是按24小时顺序比较的，9:00:00比8:30:00晚，所以你写的BETWEEN '9:00:00' AND '8:30:00'其实是一个空区间，根本查不到数据。

我来给你拆解下解决方案，分两步修正你的查询：

1. 先修正时间范围的筛选逻辑 #

因为你的统计时段跨天，所以要把条件拆成两个部分：要么记录时间在当日9:00到23:59:59之间，要么在次日00:00:00到8:30:00之间。对应的SQL条件应该是：

timestamp::time >= '09:00:00' OR timestamp::time <= '08:30:00'

2. 调整分组逻辑，把跨天记录归到正确的统计周期 #

你原来用DATE(timestamp)分组，但比如次日凌晨2点的记录，应该属于前一天的统计周期（比如10月1日9:00到10月2日8:30这个周期里，10月2日凌晨2点的记录要算到10月1日的统计组）。所以需要用一个CASE语句，把每个时间戳映射到它所属的统计日期：

• 如果时间≥9:00，统计日期就是当天的日期
• 如果时间<9:00，统计日期就是前一天的日期

完整的Rails查询代码 #

把上面的逻辑整合起来，最终的代码应该是这样：

@rainfall = Weather.select(
  # 计算每条记录所属的统计日期
  "CASE WHEN timestamp::time >= '09:00:00' THEN DATE(timestamp) ELSE DATE(timestamp) - INTERVAL '1 day' END AS stats_date",
  "MAX(rain) as maxrain"
)
# 筛选统计时段内的记录
.where("timestamp::time >= '09:00:00' OR timestamp::time <= '08:30:00'")
# 按统计日期分组
.group("stats_date")
# 按日期排序，方便查看结果
.order("stats_date")

额外优化：如果需要指定查询的日期范围 #

如果你只想查某个时间段内的统计数据，可以在where里追加时间范围条件，比如：

@rainfall = Weather.select(...)
.where("timestamp >= ? AND timestamp <= ?", Date.parse('2024-01-01'), Date.parse('2024-01-31') + 1.day)
.where("timestamp::time >= '09:00:00' OR timestamp::time <= '08:30:00'")
.group("stats_date")
.order("stats_date")

注意这里结束时间要加1天，因为最后一个统计周期会到次日8:30，确保不会漏掉最后一天的时段数据。

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