Hive 日期函数
Hive Date Functions
官网地址:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions
函数名 | 返回类型 | 简介 |
---|---|---|
from_unixtime(bigint unixtime[, string format]) | string | 将UNIX时间戳转化为日期,可以设定日期格式 |
unix_timestamp(string date, string pattern) | bigint | 将字符串日期转化为UNIX时间戳(以秒为单位),默认字符串日期格式为yyyy-MM-dd HH:mm:ss,使用其他格式日期需要指明pattern |
to_date(string timestamp) | prev2.1.0:string 2.1.0 on:date | 返回时间戳字符串的日期部分,在2.1.0版本开始返回date类型 |
year(string date) | int | 获取日期中年份 |
quarter(date/timestamp/string) | int | 返回1到4范围内的日期(或时间戳或字符串)的所属季度 |
month(string date) | int | 获取日期中的月份 |
day(string date) dayofmonth(date) | int | 获取日期中的天数 |
hour(string date) | int | 获取日期中的小时 |
minute(string date) | int | 获取日期中的分钟 |
second(string date) | int | 获取日期中的秒 |
weekofyear(string date) | int | 获取日期在当前年份的周数 |
extract(field FROM source) | int | 提取source中的field部分 , source必须是日期、时间戳、间隔、字符串时间戳,field可以是天、星期几、小时、分钟、月、季度、秒、周和年 |
datediff(string enddate, string startdate) | int | 获取两个日期的相差天数,返回是从enddate到startdate的天数 |
date_add(date/timestamp/string startdate, tinyint/smallint/int days) | pre 2.1.0:string 2.1.0 on: date | 获取当前传入日期增加days后的日期,days可以是负数 |
date_sub(date/timestamp/string startdate, tinyint/smallint/int days) | prev2.1.0:string 2.1.0 on:date | 获取当前传入日期减少days后的日期,days可以是负数 |
from_utc_timestamp({any primitive type} ts, string timezone) | timestamp | 将UTC中的时间戳转换为给定的时区 |
to_utc_timestamp({any primitive type} ts, string timezone) | timestamp | 将给定时区中的时间戳 |
current_date | date | 返回当前日期 |
current_timestamp | timestamp | 返回当前时间戳 |
add_months(string start_date, int num_months, output_date_format) | string | 返回start_date后的num_months的日期,num_months可以是负数,也可以指定output_date_format输出格式 |
last_day(string date) | string | 返回当前日期所属月份的最后一天 |
next_day(string start_date, string day_of_week) | string | 返回日期的下个星期几的日期,day_of_week的参数为MO,TU,WE,TH,FR,SA,SU |
trunc(string date, string format) | string | 返回当前日期月初(format=MONTH/MON/MM)、年初(format=YEAR/YYYY/YY) |
months_between(date1, date2) | double | 返回日期date1和date2之间的月数 |
date_format(date/timestamp/string ts, string fmt) | string | 日期格式化成fmt格式的字符串 |
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天