Hive版本: hive-1.1.0-cdh5.14.2
1. Unix时间戳转日期:from_unixtime
语法:from_unixtime(bigint unixtime[, string format])
返回值:string
描述: Unix时间戳转换为日期格式
0: jdbc:hive2://node03:10000> select from_unixtime(1237573801, 'yyyy-MM-dd HH:mm:ss') as time_string;
+----------------------+--+
| time_string |
+----------------------+--+
| 2009-03-21 02:30:01 |
+----------------------+--+
1 row selected (0.14 seconds)
0: jdbc:hive2://node03:10000> select from_unixtime(1237573801) as time;
+----------------------+--+
| time |
+----------------------+--+
| 2009-03-21 02:30:01 |
+----------------------+--+
1 row selected (0.126 seconds)
2. 当前Unix时间戳:unix_timestamp
语法:unix_timestamp()
返回值: bigint
描述:返回当前时间的Unix时间戳
0: jdbc:hive2://node03:10000> select unix_timestamp() as current_time;
+---------------+--+
| current_time |
+---------------+--+
| 1592639728 |
+---------------+--+
3. 日期转Unix时间戳:unix_timestamp
语法: unix_timestamp(string date)
返回值: bigint
描述:返回指定日期的Unix时间戳
0: jdbc:hive2://node03:10000> select unix_timestamp('2009-03-20 11:30:01') as time_in_seconds;
+------------------+--+
| time_in_seconds |
+------------------+--+
| 1237519801 |
+------------------+--+
1 row selected (0.138 seconds)
4. 指定日期某部分转为Unix时间戳:unix_timestamp
语法:unix_timestamp(string date, string pattern)
返回值: bigint
描述:根据指定的pattern,转换日期为Unix时间戳
0: jdbc:hive2://node03:10000> select unix_timestamp('2009-03-20', 'yyyy-MM-dd') as time_in_seconds;
+------------------+--+
| time_in_seconds |
+------------------+--+
| 1237478400 |
+------------------+--+
1 row selected (0.121 seconds)
5. 时间戳转日期:to_date
语法:to_date(string timestamp)
返回值: 2.1.0版本前返回string,2.1.0版本后返回date
描述: 把指定的时间戳转换为日期格式
0: jdbc:hive2://node03:10000> select to_date("1970-01-01 00:00:00") as date1;
+-------------+--+
| date1 |
+-------------+--+
| 1970-01-01 |
+-------------+--+
1 row selected (0.141 seconds)
6. 提取日期中的年份:year
语法:year(string date)
返回值:int
描述:提取日期中的年份部分
0: jdbc:hive2://node03:10000> select year('2020-06-20 16:01:00') as year;
+-------+--+
| year |
+-------+--+
| 2020 |
+-------+--+
1 row selected (0.116 seconds)
0: jdbc:hive2://node03:10000> select year('2020-06-20') as year;
+-------+--+
| year |
+-------+--+
| 2020 |
+-------+--+
1 row selected (0.119 seconds)
7. 提取日期中的月份:month
语法:month(string date)
返回值: int
描述:提取日期中的月份部分
0: jdbc:hive2://node03:10000> select month('2020-06-20') as month;
+--------+--+
| month |
+--------+--+
| 6 |
+--------+--+
1 row selected (0.127 seconds)
0: jdbc:hive2://node03:10000> select month('2020-06-20 16:08:00') as month;
+--------+--+
| month |
+--------+--+
| 6 |
+--------+--+
1 row selected (0.112 seconds)
8. 提取日期中的日:day
语法: day(string date) 、dayofmonth(date)
返回值: int
描述:提取日期中的日部分
0: jdbc:hive2://node03:10000> select day('2020-06-20') as day;
+------+--+
| day |
+------+--+
| 20 |
+------+--+
1 row selected (0.189 seconds)
0: jdbc:hive2://node03:10000> select dayofmonth('2020-06-20') as day;
+------+--+
| day |
+------+--+
| 20 |
+------+--+
1 row selected (0.106 seconds)
9. 提取日期中的小时:hour
语法:hour(string date)
返回值:int
描述:提取日期中的小时部分
0: jdbc:hive2://node03:10000> select hour('2020-06-20 16:08:00') as hour;
+-------+--+
| hour |
+-------+--+
| 16 |
+-------+--+
1 row selected (0.113 seconds)
0: jdbc:hive2://node03:10000> select hour('16:08:00') as hour;
+-------+--+
| hour |
+-------+--+
| 16 |
+-------+--+
10. 提取日期中的分钟/秒:minute/second
语法:minute(string date) / second(string date)
返回值: int
描述:提取日期中的分钟/秒部分
0: jdbc:hive2://node03:10000> select hour('2020-06-20 16:08:00') as hour,
. . . . . . . . . . . . . . > minute('2020-06-20 16:08:00') as monute,
. . . . . . . . . . . . . . > second('2020-06-20 16:08:00') as second
. . . . . . . . . . . . . . > ;
+-------+---------+---------+--+
| hour | monute | second |
+-------+---------+---------+--+
| 16 | 8 | 0 |
+-------+---------+---------+--+
11. 返回日期处于当年的第几周:weekofyear
语法:weekofyear(string date)
返回值:int
描述: 返回指定日期处于当年的第几周
0: jdbc:hive2://node03:10000> select weekofyear('2020-06-20') as weekofyear;
+-------------+--+
| weekofyear |
+-------------+--+
| 25 |
+-------------+--+
1 row selected (0.139 seconds)
12. 两日期间隔天数:datediff
语法:datediff(string enddate, string startdate)
返回值: int
描述: 返回两指定日期之间的天数
0: jdbc:hive2://node03:10000> select datediff('2020-06-20', '2020-06-15') as days;
+-------+--+
| days |
+-------+--+
| 5 |
+-------+--+
1 row selected (0.138 seconds)
13. 日期增加函数: dateadd
语法:date_add(date/timestamp/string startdate, tinyint/smallint/int days)
返回值: 2.1.0版本前返回string,2.1.0版本后返回date
描述:返回开始日期startdate增加天数days后的日期
0: jdbc:hive2://node03:10000> select date_add('2020-06-15', 5) as date;
+-------------+--+
| date |
+-------------+--+
| 2020-06-20 |
+-------------+--+
1 row selected (0.14 seconds)
14. 日期减少函数: datesub
语法: date_sub(date/timestamp/string startdate, tinyint/smallint/int days)
返回值: 2.1.0版本前返回string,2.1.0版本后返回date
描述: 返回开始日期startdate减少天数days后的日期
0: jdbc:hive2://node03:10000> select date_sub('2020-06-20', 5) as date;
+-------------+--+
| date |
+-------------+--+
| 2020-06-15 |
+-------------+--+
1 row selected (0.128 seconds)
15. 当前日期/当前时间戳:current_date / current_timestamp
语法: current_date / current_timestamp
返回值:date / timestamp
描述: 返回当前日期或者时间戳
0: jdbc:hive2://node03:10000> select current_date as date, current_timestamp as timestamp;
+-------------+--------------------------+--+
| date | timestamp |
+-------------+--------------------------+--+
| 2020-06-20 | 2020-06-20 18:19:35.736 |
+-------------+--------------------------+--+
1 row selected (0.145 seconds)
16. 月份增加函数:add_months
语法: add_months(string start_date, int num_months, output_date_format)
返回值: string
描述: 返回开始日期start_date增加num_months月后的日期,output_date_format做为可选参数只在Hive 4.0.0版本后可用。
0: jdbc:hive2://node03:10000> select add_months('2020-06-30', 1) as add_months;
+-------------+--+
| add_months |
+-------------+--+
| 2020-07-31 |
+-------------+--+
1 row selected (0.138 seconds)
17. 当月最后一天:last_day
语法:last_day(string date)
返回值: string
描述: 返回指定日期所在月份的最后一天
0: jdbc:hive2://node03:10000> select last_day(current_date) as last_day;
+-------------+--+
| last_day |
+-------------+--+
| 2020-06-30 |
+-------------+--+
1 row selected (0.117 seconds)
0: jdbc:hive2://node03:10000> select last_day(current_timestamp) as last_day;
+-------------+--+
| last_day |
+-------------+--+
| 2020-06-30 |
+-------------+--+
1 row selected (0.105 seconds)
18. 下一个周几的日期:next_day
语法: next_day(string start_date, string day_of_week)
返回值: string
描述: 返回开始日期start_date后的第一个周几(如周一)的日期,其中day_of_week可以为星期的2或3位缩写如:Mo, tue,也可以是星期的全拼,如:FRIDAY
0: jdbc:hive2://node03:10000> select next_day('2020-06-20', 'MON') as next_monday;
+--------------+--+
| next_monday |
+--------------+--+
| 2020-06-22 |
+--------------+--+
1 row selected (0.16 seconds)
19. 截断日期:trunc
语法:trunc(string date, string format)
返回值: string
描述:按指定格式截断日期,format可以为月或年:MONTH/MON/MM, YEAR/YYYY/YY
0: jdbc:hive2://node03:10000> select trunc('2020-06-20', 'MM') as trunc_mon;
+-------------+--+
| trunc_mon |
+-------------+--+
| 2020-06-01 |
+-------------+--+
1 row selected (0.133 seconds)
0: jdbc:hive2://node03:10000> select trunc('2020-06-20', 'YEAR') as trunc_date;
+-------------+--+
| trunc_date |
+-------------+--+
| 2020-01-01 |
+-------------+--+
1 row selected (0.164 seconds)
20. 计算间隔月份:months_between
语法:months_between(date1, date2)
返回值: double
描述: 返回两日期的间隔月份,精度到小数点后最多8位
0: jdbc:hive2://node03:10000> select months_between('2020-06-20','2020-06-01') as months_between1,
. . . . . . . . . . . . . . > months_between('2020-06-20','2020-06-20') as months_between2,
. . . . . . . . . . . . . . > months_between('2020-06-20','2020-05-20') as months_between3,
. . . . . . . . . . . . . . > months_between('2020-06-20','2020-07-20') as months_between4;
+------------------+------------------+------------------+------------------+--+
| months_between1 | months_between2 | months_between3 | months_between4 |
+------------------+------------------+------------------+------------------+--+
| 0.61290323 | 0.0 | 1.0 | -1.0 |
+------------------+------------------+------------------+------------------+--+
1 row selected (0.127 seconds)