PostgreSQL中最近30天完成行程ETA差值90分位数计算及日期验证

问题背景 #

现有一张存储出租车行程数据的SQL表，表结构如下：

id            integer
client_id     integer (外键关联至events.rider_id)
driver_id     integer
city_id       integer (外键关联至cities.city_id)
client_rating integer
driver_rating integer
predicted_eta integer
actual_eta    integer
first_completed_date Timestamp,
status        Enum('completed', 'cancelled_by_driver', 'cancelled_by_client')

需求：计算最近30天内所有已完成行程的Actual ETA与Predicted ETA差值的90分位数，给出的SQL语句如下：

select
  percentile_cont(0.90) within group (order by actual_eta-predicted_eta) as percentile_90_diff
from trips t where status='completed'
and first_completed_date > (first_completed_date - INTERVAL '30 DAY')::DATE

疑问：语句中筛选最近30天的条件first_completed_date > (first_completed_date - INTERVAL '30 DAY')::DATE是否正确？

条件正确性分析 #

这个筛选条件完全错误，根本无法实现“筛选最近30天数据”的效果。

原因很直接：对于任意一条数据的first_completed_date值，first_completed_date - INTERVAL '30 DAY'必然是一个比当前字段值早30天的时间，因此first_completed_date > (first_completed_date - INTERVAL '30 DAY')::DATE这个条件永远为真，相当于没有添加任何时间筛选规则，会把所有已完成的行程都纳入计算，完全不符合需求。

正确的筛选方式 #

要筛选最近30天的已完成行程，需要基于当前时间做时间范围判断，常见的正确写法有两种：

1. 保留时分秒精度的精确筛选：

first_completed_date >= CURRENT_TIMESTAMP - INTERVAL '30 DAY'

2. 仅基于日期维度的筛选（忽略时分秒）：

first_completed_date::DATE >= CURRENT_DATE - INTERVAL '30 DAY'

如果业务涉及多时区场景，建议明确指定时区（例如CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Shanghai'），避免时区偏差导致的筛选结果错误。

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