调度应用SQL查询仅返回每7天增量而非每日数据的问题求助

问题根源 #

你的查询仅返回当前日期及每7天增量的时段，核心原因是字符串匹配星期名称时的空格问题：

• to_char(d.date, 'Day')返回的星期名称会自动填充尾部空格（PostgreSQL默认填充至9个字符，比如'Sunday '），而你数据库中schedule_blocks.day_of_week存储的是无空格的字符串（比如'Sunday'），导致ILIKE仅能匹配到与当前日期星期名称格式完全一致的记录，其他日期的匹配全部失败。
• 此外，用字符串匹配星期名称存在本地化风险（不同地区语言的星期名称不同），稳定性差。

修正方案 #

改用**数字格式的星期（ISODOW，1=周一，7=周日）**进行关联，同时简化日期生成逻辑，避免不必要的递归CTE：

WITH dates AS (
  -- 直接生成未来2个月的所有日期，同时获取对应的ISODOW星期数字
  SELECT
    gen_date.date,
    EXTRACT(ISODOW FROM gen_date.date)::int AS day_of_week
  FROM (
    SELECT generate_series(
      CURRENT_DATE,
      CURRENT_DATE + INTERVAL '2 months',
      INTERVAL '1 day'
    )::date AS date
  ) gen_date
),
schedule_blocks_with_dates AS (
  SELECT
    sb.*,
    d.date AS block_date
  FROM
    schedule_blocks sb
  JOIN dates d 
    -- 将schedule_blocks中的星期名称转换为ISODOW数字，与dates中的数字关联
    ON EXTRACT(ISODOW FROM to_date(sb.day_of_week, 'Day'))::int = d.day_of_week
  WHERE
    sb.is_available = TRUE -- 注意：若你的表中无该字段，请删除此条件
)
SELECT
  block_id,
  user_id,
  block_date AS date,
  start_time,
  end_time
FROM
  schedule_blocks_with_dates
ORDER BY
  date;

额外优化建议 #

1. 存储星期数字替代字符串：建议将schedule_blocks.day_of_week字段改为存储ISODOW数字（1-7），彻底避免字符串匹配的问题，同时提升查询性能。
2. 适配不同星期格式：若你的day_of_week存储的是缩写（如'Mon'）或非英文名称，需调整to_date的格式符：
   • 缩写：to_date(sb.day_of_week, 'Dy')
   • 中文星期：to_date(sb.day_of_week, 'FMDay')（FM用于去除尾部空格）

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