Impala生成周日期范围字符串时跨月日期显示错误求助

我需要在Impala中生成形如「W202243 from 24-Oct a 30-Oct」的周日期范围字符串（周期为周一至周日），为此编写了以下SQL查询语句：

select distinct concat('W',substr(cast(a.day_key AS string),1,4), IF (DAYOFWEEK(from_unixtime(unix_timestamp(cast(cast(a.day_key AS string) AS string), "yyyyMMdd")))=2, cast(weekofyear(from_unixtime(unix_timestamp(cast(cast(a.day_key AS string) AS string), "yyyyMMdd")))-1 AS string), cast(weekofyear(from_unixtime(unix_timestamp(cast(cast(a.day_key AS string) AS string), "yyyyMMdd"))) AS string)), ' from ', SUBSTR(substr(cast(TRUNC(date_add(from_unixtime(unix_timestamp(cast(a.day_key AS string), "yyyyMMdd")),-1),'DY') AS string), 9,10),1,2), '-', SUBSTR(MONTHNAME(date_add(TRUNC(date_add(from_unixtime(unix_timestamp(cast(a.day_key AS string), "yyyyMMdd")),-1),'DY'),1)),1,3),' a ', SUBSTR(substr(cast(date_add(TRUNC(date_add(from_unixtime(unix_timestamp(cast(a.day_key AS string), "yyyyMMdd")),-1),'DY'),6) AS string), 9,10),1,2), '-', SUBSTR(MONTHNAME(date_add(TRUNC(date_add(from_unixtime(unix_timestamp(cast(a.day_key AS string), "yyyyMMdd")),-1),'DY'),6)),1,3))

目前结果基本正确，但当周的首日同时为月末时，会出现错误结果，例如生成「W202244 FROM 31-Nov a 06-Nov」，其中月份应为十月而非十一月，请问需在查询中添加什么逻辑来解决该问题？

错误根源 #

原SQL中计算周起始日时，先对日期做date_add(..., -1)再用TRUNC(..., 'DY')截断，后续又错误地给截断后的日期加1天，导致月末/月初的日期出现月份偏移（比如10月31日被错误处理成11月的日期）。同时多层字符串截取极易出错，逻辑冗余复杂。

修复后的SQL #

核心思路是直接定位当前日期所在周的周一（起始日）和周日（结束日），用Impala内置的日期函数简化计算，避免手动字符串操作：

SELECT DISTINCT
  CONCAT(
    'W',
    YEAR(week_start),
    LPAD(WEEKOFYEAR(week_start), 2, '0'),
    ' from ',
    DATE_FORMAT(week_start, '%d-%b'),
    ' a ',
    DATE_FORMAT(week_end, '%d-%b')
  ) AS week_range_str
FROM (
  SELECT
    a.day_key,
    -- 获取当前日期所在ISO周的周一（ISO周以周一为起始）
    TRUNC(FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(a.day_key AS STRING), 'yyyyMMdd')), 'IW') AS week_start,
    -- 周一加6天得到当周周日
    DATE_ADD(TRUNC(FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(a.day_key AS STRING), 'yyyyMMdd')), 'IW'), 6) AS week_end
  FROM your_table a -- 替换成你的实际表名
) t;

关键优化点 #

• 用TRUNC(..., 'IW')精准定位周起始：Impala中IW代表ISO标准周，直接返回当前日期所在周的周一，无需手动调整日期，彻底避免月末/月初的偏移错误。
• 用DATE_FORMAT简化日期格式化：直接生成DD-Mon格式的字符串，替代原SQL中多层SUBSTR截取，代码更简洁且不易出错。
• 周数基于周起始日计算：确保周数和周范围完全匹配，替代原SQL中根据当前日期调整周数的复杂逻辑。
• 周结束日通过起始日加6天得到：严格保证周期是周一至周日，逻辑清晰。

验证示例 #

针对10月31日（周一，属于W202244），会正确生成：W202244 from 31-Oct a 06-Nov，月份显示完全正确。

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