hive获取这周五到下周四的区间，周一到周日的区间

-- 获取每个日期所在周期的开始和结束时间
SELECT
    created_date AS date_in_period,
    CASE
        WHEN date_format(created_date, 'u') < 5 THEN date_sub(created_date, cast(date_format(created_date, 'u') AS INT) + 2)
        ELSE date_sub(created_date, cast(date_format(created_date, 'u') AS INT) - 5)
    END AS period_start,
    CASE
        WHEN date_format(created_date, 'u') < 5 THEN date_add(created_date, 4 - cast(date_format(created_date, 'u') AS INT))
        ELSE date_add(created_date, 11 - cast(date_format(created_date, 'u') AS INT))
    END AS period_end,
    date_format(created_date, 'u') actual_week
FROM ods_ticket_full
where dt = '2024-05-10';

获取周一到周日的星期区间

with t1 as (
    select sp_no,
           sp_name,
           item,
           applyer,
           date_format(from_utc_timestamp(apply_time * 1000, 'America/Los_Angeles'),'yyyy-MM-dd HH:mm:ss')   apply_time_pst,
           date_format(from_utc_timestamp(apply_time * 1000, 'Asia/Shanghai'), 'yyyy-MM-dd HH:mm:ss') apply_time_cst,
           department_name,
           expect_install_time,
           approver,
           date_format(from_utc_timestamp(approver_time * 1000, 'Asia/Shanghai'), 'yyyy-MM-dd HH:mm:ss') approver_time_cst
    from ods_installation_timeout_full
    where dt = '2024-05-13'
)
-- insert overwrite table ads_installation_timeout
select sp_no,
       sp_name,
       item,
       applyer,
       apply_time_pst,
       apply_time_cst,
       department_name,
       expect_install_time,
       approver,
       approver_time_cst,
       concat(year(date_sub(next_day(approver_time_cst, 'MO'), 4)), '-', if(weekofyear(approver_time_cst)<10,concat('0',weekofyear(approver_time_cst)),weekofyear(approver_time_cst)))  actual_week,
        -- 获取每个日期所在周期的开始和结束时间
       -- 对于输入的日期，start_of_week将调整到所在周的周一，end_of_week将调整到所在周的周日。
       date_sub(approver_time_cst, cast(date_format(approver_time_cst, 'u') AS INT) - 1) AS start_date, -- 当日期为周一时，返回自身；否则返回本周周一
       date_add(approver_time_cst, 7 - cast(date_format(approver_time_cst, 'u') AS INT)) AS end_date,-- 当日期为周日时，返回自身；否则返回本周周日
       concat(date_format(approver_time_cst,'yyyy-MM'),'-01') actual_month,
       concat(year(approver_time_cst),'-',quarter(approver_time_cst)) actual_quarter
from t1
where datediff(expect_install_time,approver_time_cst)<