Hive 日期函数

Hive Date Functions

官网地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions

1.日期 与 时间戳 相互转换函数

-- 时间 戳转 日期
select from_unixtime(1639464603); -- 2021-12-14 06:50:03
select from_unixtime(1639464603, 'yyyyMMdd');  --20211214
select from_unixtime(1639464603, 'yyyy-MM-dd');  --2021-12-14

-- 日期 转 时间戳
-- unix_timestamp函数
select unix_timestamp('2021-12-14 13:14:52');   --结果为 1639487692
select unix_timestamp('2021-12-14');  --默认格式为 'yyyy-MM-dd HH:mm:ss', 所以 不符合 结果为 null
select unix_timestamp('2021-12-14', 'yyyy-MM-dd');  --结果为 1639440000
-- to_date函数: 获取日期部分
select to_date('2021-12-14 13:14:52');

-- 自定义格式化日期,可以对接java的simpledateformat等
select date_format('2021-12-14', 'yyyy-MM-dd');


-- 特别地
-- 将不同时区的时间进行转换
/*
	时间戳:表示以(1970-01-01 00:00:00)为起点，到现在的秒数。
	GMT:即格林尼治标准时间，也就是世界时。
	UTC:即协调世界时。UTC现在作为世界标准时间使用。
	时区:全球划分为24个时区(东、西各12个时区),英国（格林尼治天文台旧址）为零时区（GMT+00）,中国北京处于东8区,若是英国时间为6点整，则GMT时间为6点整，则北京时间为14点整。
	世界时区英文缩写: 百度
*/
select from_utc_timestamp('1970-01-01 00:00:00', 'PRC'); --结果为 1970-01-01 08:00:00.000000000

select to_utc_timestamp(timestamp '1970-01-30 16:00:00','PST'); --结果为1970-01-31 00:00:00.000000000

2.获取年、季节、月、日、周数、星期几、小时、分钟、秒

-- 获取当前时间
select `current_date`();   
select current_date;    -- 以下的 current_date 都是 '2021-12-14'
select `current_timestamp`();
select current_timestamp;

-- 年
-- 1.获取年份
select year(current_date);  --结果: 2021
select year(`current_timestamp`());
select extract(YEAR from current_date);
-- 2.获取年初
select trunc(current_date, 'YY');  --结果: 2021-01-01
-- 3.获取年末
select date_sub(add_months(trunc(current_date, 'YY'), 12), 1);  --结果: 2021-12-31

-- 季节
-- 1.获取当前日期所属季节
select quarter(current_date); --结果: 4
-- 2.获取季节的开始日期
select concat(year(current_date), '-', substr(concat('0', (quarter(current_date) - 1) * 3 + 1), -2), '-01');  --结果: 2021-10-01
-- 3.获取季节的结束日期
select date_sub(concat(year(current_date), '-', substr(concat('0', quarter(current_date) * 3 + 1), -2), '-01'), 1);  --结果: 2021-12-31

-- 月份
-- 1.获取当前日期所属月份
select month(current_date);  --结果:  12 
select extract(MONTH from current_date);   --结果:  12
-- 2.获取当前月份的开始日期
select trunc(current_date, 'MM');   --结果: 2021-12-01
-- 3.获取当前月份的结束日期
select last_day(current_date);   --结果: 2021-12-31
-- 4.获取两个日期相差月数
select months_between(current_date, '1970-01-01');  -- 有31   --结果: 623.41935484

-- 天数(日)
-- 1.获取日期中的天数
select day(current_date);   --结果: 14
select dayofmonth(current_date);   --结果: 14
select extract(DAY from current_date);   --结果: 14
-- 2.获取两个日期相差天数
select datediff(current_date, '2020-01-01');   --结果: 713
select datediff('2020-01-01', current_date);   --结果: -713

-- 周数
-- 1.获取当前日期所属年份的周数
select weekofyear(current_date);  --结果为50
select weekofyear('2020-01-08');  --结果为2
select extract(WEEK from current_date);
select extract(WEEK from '2020-01-08'); --结果为2
-- 2.获取当前日期所属月份的周数 
select mod(weekofyear(current_date), month(current_date));  --结果为2

-- 星期数
-- 1.获取当前日期所属星期数
select `dayofweek`('2021-12-17');   --周五 结果为6
select `dayofweek`('2021-12-18');   --周六 结果为7
select `dayofweek`('2021-12-19');   --周日 结果为1
select extract(DAYOFWEEK  from '2021-12-17');  --周五 结果为6
select extract(DAYOFWEEK  from '2021-12-18');  --周六 结果为7
select extract(DAYOFWEEK  from '2021-12-19');  --周日 结果为1
select
       case `dayofweek`('2021-12-14')
            when 2 then '周一'
            when 3 then '周二'
            when 4 then '周三'
            when 5 then '周四'
            when 6 then '周五'
            when 7 then '周六'
            when 1 then '周日'
       end;
select
       case pmod(datediff('2021-12-14', '2021-12-13'), 7) --'2021-12-13'是周一
            when 0 then '周一'
            when 1 then '周二'
            when 2 then '周三'
            when 3 then '周四'
            when 4 then '周五'
            when 5 then '周六'
            when 6 then '周日'
       end;
-- 2.获取下一个周几
-- day_of_week的参数为MO,TU,WE,TH,FR,SA,SU
select next_day('2021-12-14', 'SU');  -- 都是以周日为开始,所以下一个周日是2021-12-19
-- 2.获取上一个周几,先获取下一个周几再减7
select date_sub(next_day('2021-12-14', 'SU'), 7);  -- 都是以周日为开始,所以上一个周日是2021-12-12


-- 小时
select hour(current_timestamp);
select hour('1970-01-01 13:14:52');   --结果:  13

-- 分钟
select minute(current_timestamp);
select minute('1970-01-01 13:14:52');  --结果: 14

-- 秒
select second(current_timestamp);
select second('1970-01-01 13:14:52');  --结果: 52

3.日期增减

-- 日期相关
-- 求明天
select date_add(current_date, 1);  --current_date是 2021-12-14 结果是 2021-12-15
select date_sub(current_date, -1);
-- 求昨天
select date_add(current_date, -1);  --current_date是 2021-12-14 结果是 2021-12-13
select date_sub(current_date, 1);
-- 求两天相差多少天
select datediff('2021-12-15', '2021-12-01');  --结果是 14

-- 月份相关
-- 下一个月的day
select add_months('2021-12-14', 1);  --结果是 2022-01-14
select add_months('2021-01-30', 1);  --2月份没有30号,结果是 2021-02-28
-- 上一个月的day
select add_months('2021-12-14', -1);  --结果是 2021-11-14
select add_months('2021-12-31', -1);  --11月份没有31号,结果是 2021-11-30
-- 计算两个日期相隔的月数  -- 不建议该操作
select months_between('2021-01-15', '2021-12-14');  --会将31号等加天数加入,所以结果会有小数,结果是 -10.96774194
select datediff('2021-01-15', '2021-12-14') / 30; --结果是 -11.1 不准确,原因是有的月份是31天