Amazon Athena去年当月数据查询及SQL时间区间错误修正

要提取从去年当月第一天到当前月最后一天的数据，直接用Athena的日期函数构造时间范围即可，避免字符串转换带来的误差：

SELECT *
FROM your_table
WHERE submission_date 
    BETWEEN date_trunc('month', current_date - interval '1' year)
    AND last_day(current_date);

• 当当前日期为2023-09-23时，date_trunc('month', current_date - interval '1' year)返回2022-09-01，last_day(current_date)返回2023-09-30，正好匹配需求区间。
• 如果submission_date是字符串类型，需先转为TIMESTAMP：CAST(submission_date AS TIMESTAMP)

原SQL出错是因为日期格式不统一：START_DAY转成了带横杠的日期字符串（如2022-12-01），而submission_date和Current_MONTH是yyyymm格式的字符串，字符串比较时因符号导致范围判断错误。修正方法是统一所有日期格式为yyyymm：

WITH START_DAY AS (
    SELECT TO_CHAR(date_trunc('month', current_date - interval '14' month), 'yyyymm') AS START_DAY
),
Current_MONTH AS (
    SELECT TO_CHAR(CURRENT_DATE, 'yyyymm') AS Current_MONTH
),
TABLE1 AS (
    SELECT *
    FROM your_table
    WHERE TO_CHAR(CAST(submission_date AS TIMESTAMP), '%Y%m') 
        BETWEEN (SELECT start_day FROM start_day) 
        AND (SELECT Current_MONTH FROM Current_MONTH)
)
SELECT *
FROM table1;

• 核心修正：将START_DAY的转换改为TO_CHAR(date_trunc(...), 'yyyymm')，确保和右侧日期格式完全一致，字符串比较逻辑正确。
• 更优写法：直接用日期类型比较（避免转字符串），简化查询：

SELECT *
FROM your_table
WHERE CAST(submission_date AS TIMESTAMP)
    BETWEEN date_trunc('month', current_date - interval '14' month)
    AND last_day(current_date);

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