hive函数dayofweek的奇怪用法

hive函数dayofweek的奇怪用法

1.单独使用

select `dayofweek`('2023-07-03'); 周一 2
select `dayofweek`('2023-07-04'); 周二 3
select `dayofweek`('2023-07-05'); 周三 4
select `dayofweek`('2023-07-06'); 周四 5
select `dayofweek`('2023-07-07'); 周五 6
select `dayofweek`('2023-07-08'); 周六 7
select `dayofweek`('2023-07-09'); 周日 1

发现是1-星期天；2-星期一；3-星期二；以此类推

2.如果是使用的是表的时间列

select
    cur_date,
    `dayofweek`(cur_date)
from (
         select date_format(cur_date, 'yyyy-MM-dd') cur_date
         from ods_cn_attendance_day_print_full
         where dt = '2023-07-05'
         group by date_format(cur_date, 'yyyy-MM-dd')
     )t1
order by cur_date desc
limit 10;

得到的结果是周一是1，周二是2，周三3，…周日是7

所以这种结果很奇怪，我后面又试了几次，发现hive的dayofweek得到的结果还会变化，所以最保险的做法就是：
再创建一张表，然后将dayofweek加入该表中

重新建表

drop table if exists hr_cn.dwd_employee_daily_punch_record_detail_cross_day2;
create external table IF NOT EXISTS hr_cn.dwd_employee_daily_punch_record_detail_cross_day2
(
    staff_id string comment '员工id',
    user_name string comment '员工姓名',
    finger_print_number             string comment '员工指纹',
    group_name string comment '员工所属组别',
    leader_name string comment '组长',
    actual_date              string comment '打卡日期',
    cur_week int comment '打卡日期所属的星期',
    Actual_In                string comment '上班签入时间',
    Actual_Out               string comment '下班签出时间',
    Acutal_In_BF             string comment '早餐签入时间',
    Acutal_Out_BF            string comment '早餐签出时间',
    Acutal_In_Lunch          string comment '午餐签入时间',
    Acutal_Out_Lunch         string comment '午餐签出时间',
    Acutal_In_Dinner string comment '宵夜签入时间',
    Acutal_Out_Dinner        string comment '宵夜签出时间',
    Acutal_In_Work_Overtime  string comment '加班签入时间',
    Acutal_Out_Work_Overtime string comment '加班签出时间',
    Mobile_Officing_In       string comment '移动办公签入时间',
    Mobile_Officing_Out      string comment '移动办公签出',
    department string comment '部门',
    position_status int comment '在职状态，1代表在职，0代表离职',
    punch_type int comment '班次类型 1-日班；2-夜班',
    title string comment '班次名'
) comment '员工日班每天打卡记录表'
partitioned by (dt string)
row format delimited fields terminated by '\001'
NULL DEFINED AS ''
stored as orc
LOCATION '/warehouse/hr_cn/dwd/dwd_employee_daily_punch_record_detail_cross_day2';

insert overwrite table dwd_employee_daily_punch_record_detail_cross_day2 partition (dt='2023-07-06')
select staff_id,
       user_name,
       finger_print_number,
       group_name,
       leader_name,
       actual_date,
       dayofweek(actual_date) cur_week,
       Actual_In,
       Actual_Out,
       Acutal_In_BF,
       Acutal_Out_BF,
       Acutal_In_Lunch,
       Acutal_Out_Lunch,
       Acutal_In_Dinner,
       Acutal_Out_Dinner,
       Acutal_In_Work_Overtime,
       Acutal_Out_Work_Overtime,
       Mobile_Officing_In,
       Mobile_Officing_Out,
       department,
       position_status,
       punch_type,
       title
from dwd_employee_daily_punch_record_detail_cross_day
where dt = '2023-07-06';