[SQL] Hive日期时间函数整理
1. 日期时间常量
current_date：获取当前日期
current_timestamp：获取当前时间
2. 日期时间函数
2.1 获取日期时间指定部分
- 获取日期指定部分
extract(field FROM source)
-- 返回值类型：int
-- source: date, timestamp, interval, 可转化为date或timestamp的string
-- field: day, dayofweek, hour, minute, month, quarter, second, week, year
select extract(year from "1970-01-01 00:00:00");
> 1970
select extract(month from "1970-01-01");
> 1
select extract(hour from "2020-05-20 07:10:35");
> 7
select extract(month from interval '1-3' year to month);
> 3
select extract(minute from interval '3 12:20:30' day to second);
> 20
- 获取截至指定单元的日期
trunc(string date, string format)
-- 返回值类型：string
-- format: MONTH/MON/MM, YEAR/YYYY/YY
select trunc('2015-03-17', 'MM');
> '2015-03-01'
- 获取年份
year(string date)
-- 返回值类型：int
select year("1970-01-01 00:00:00");
> 1970
select year("1970-01-01");
> 1970
- 获取月份
month(string date)
-- 返回值类型：int
select month("1970-01-01 00:00:00");
> 1
select month("1970-11-01");
> 11
- 获取日期
day(string date)dateofmonth(date)
-- 返回值类型：int
select day("1970-01-01 00:00:00");
> 1
select day("1970-11-01");
> 1
select dayofmonth("1970-11-27");
> 27
- 获取季度
quarter(date/timestamp/string)
-- 返回值类型：int
select quarter('2022-06-01');
> 2
- 获取周数
weekofyear(string date)
-- 返回值类型：int
select weekofyear("1970-11-01 00:00:00");
> 44
- 获取时分秒
hour(string date)minute(string date)second(string date)
-- 返回值类型：int
select hour('2009-07-30 12:58:59');
> 12
select minute('11:05:20');
> 5
select second('11:05:20');
> 20
2.2 日期格式转换
- 时间戳转换为日期
to_date(string timestamp)
-- 返回值类型：string
select to_date("1970-01-01 00:00:00");
> "1970-01-01"
- 日期格式化
date_format(date/timestamp/string ts, string fmt)
-- 返回值类型：string
select date_format('2015-04-08', 'y');
> '2015'
- UTC时间戳转指定时区时间
from_utc_timestamp({any primitive type}*, string timezone)
-- 返回值类型：timestamp
select from_utc_timestamp('1970-01-01 08:00:00','PST');
> '1970-01-01 00:00:00'
select from_utc_timestamp(0.123,'PST');
> 1969-12-31 16:00:00.123
- 指定时区时间转UTC时间戳
to_utc_timestamp({any primitive type}*, string timezone)
-- 返回值类型：timestamp
select to_utc_timestamp('1970-01-01 00:00:00','PST');
> '1970-01-01 08:00:00'
select to_utc_timestamp(0.123,'PST');
> '1970-01-01 08:00:00.123'
- Unix时间戳转日期
from_unixtime(bigint unixtime[, string format])
-- 返回值类型：string
select from_unixtime('1660822225', 'yyyyMMdd');
> 20220718
- 获取当前Unix时间戳
unix_timestamp()
-- 返回值类型：bigint
select unix_timestamp();
> 1658115232
- 日期转Unix时间戳
unix_timestamp(string date[, string pattern])
-- 返回值类型：bigint
select unix_timestamp('2009-03-20 11:30:01');
> 1237573801
-- 给定字符串模式
select unix_timestamp('2009-03-20', 'yyyy-MM-dd');
> 1237532400
2.4 日期计算
- 计算日期差
datediff(string enddate, string startdate)
-- 返回值类型：int
select datediff('2009-03-01', '2009-02-27');
> 2
- 计算月份差
months_between(date1, date2)
-- 返回值类型：double
-- date1 - date2
select months_between('1997-02-28 10:30:00', '1996-10-30');
> 3.94959677
- 日期增减
date_add(date/timestamp/string startdate, tinyint/smallint/int days)date_sub()
-- 返回值类型：string (pre 2.1.0), date (2.1.0 on)
select date_add('2022-07-01', 1);
> '2022-07-02'
select date_sub('2022-07-01', 2);
> '2022-06-29'
- 月份增减
add_months(string start_date, int num_months)
需要注意对于日期的处理
select add_months('2022-01-31', 1);
> '2022-02-28'
select add_months('2022-02-10', 20);
> '2023-10-10'
- 获取每月最后一日
last_day(string date)
select last_day('2022-02-02');
> '2022-02-28'
