selectday,(day,1) -- 时间,date_add(day,1 - dayofweek(day))as week_first_day -- 本周第一天_周日,date_add(day,7 - dayofweek(day))as week_last_day -- 本周最后一天_周六,date_add(day,1 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as week_first_day -- 本周第一天_周一,date_add(day,7 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as week_last_day -- 本周最后一天_周日-- dayofweek在hive2.2.0开始支持，低版本的hive原生未提供dayofweek函数(获取一个日期是星期几的方法)，-- 所以只有我们自己编写udf函数提供，udf就不说了，在这里给出了一个使用hive原生函数获取星期几的技巧。,date_format(from_unixtime(unix_timestamp('0808','yyyyMMdd'),'yyyy-MM-dd'),'u') as dayofweek1 --本周第几天 u必须小写,date_format('-08-08','u') --本周第几天 u必须小写,7- datediff(next_day('-08-07',"Sunday"),'-08-07') --另外一种方式实现本周几,dayofmonth(day,1)--本月本月第几天,next_day(day,'TU') as next_tuesday-- 当前日期的下一个周二 有可能还是本周的,next_day('-08-07','Mon') as next_Monday-- 当前日期的下一个星期一 有可能还是本周的,next_day('-08-07','Tue') as next_Tuesday-- 当前日期的下一个星期二 有可能还是本周的,next_day('-08-07','Wed') as next_Wednesday -- 当前日期的下一个星期三 有可能还是本周的,next_day('-08-07','Thu') as next_Thursday -- 当前日期的下一个星期四 有可能还是本周的,next_day('-08-07','Fri') as next_Friday-- 当前日期的下一个星期五 有可能还是本周的,next_day('-08-07','Sat') as next_Saturday -- 当前日期的下一个星期六 有可能还是本周的,next_day('-08-07','Sun') as next_Sunday-- 当前日期的下一个星期日 有可能还是本周的,to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3 -2,2,0),'-01'))as season_first_day -- 当季第一天,trunc(day,'MM') as month_first_day -- 当月第一天,trunc(day,'YY') as year_first_day -- 当年第一天,last_day(add_months(trunc(day,'YY'),12)) as year_last_day -- 当年最后一天,last_day(day) as month_last_day -- 当月最后一天,last_day(to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3,2,0),'-01'))) as season_last_day -- 当季最后一天,weekofyear(day) as weekofyear1 -- 当年第几周,weekofyear('-08-07') - weekofyear(trunc('-08-07','MM')) +1as weekofmonth1 --当月第几周,second(day) as second -- 秒钟,minute(day) as minute -- 分钟,hour(day) as hour -- 小时,day(day) as day -- 日期,month(day) as month -- 月份,lpad(ceil(month(day)/3),2,0) as season -- 季度,year(day) as year -- 年份from (select '-01-02 01:01:01' as day union allselect '-02-02 02:03:04' as day union allselect '-03-02 03:05:07' as day union allselect '-04-02 04:07:10' as day union allselect '-05-02 05:09:13' as day union allselect '-06-02 06:11:16' as day union allselect '-07-02 07:13:19' as day union allselect '-08-02 08:15:22' as day union allselect '-09-02 09:17:25' as day union allselect '-10-02 10:19:28' as day union allselect '-11-02 11:21:31' as day union allselect '-12-02 12:23:34' as day) t1;
