HIVE-其他函数(具体使用)

最新推荐文章于 2024-03-22 15:20:04 发布

梦里Coding

最新推荐文章于 2024-03-22 15:20:04 发布

阅读量474

点赞数

分类专栏： Hive 文章标签： hive 其他 big data

本文链接：https://blog.csdn.net/weixin_43586713/article/details/120823107

版权

Hive 专栏收录该内容

43 篇文章 6 订阅

订阅专栏

UXIX_TIMESTAMP:返回当前时间戳

首先查看该函数的用法:

hive (default)> DESC FUNCTION UNIX_TIMESTAMP;
OK
tab_name
UNIX_TIMESTAMP([date[, pattern]]) - Returns the UNIX timestamp
Time taken: 0.122 seconds, Fetched: 1 row(s)

举例使用:

//获取当前时间戳
hive (default)> SELECT UNIX_TIMESTAMP();
OK
_c0
1634527220
Time taken: 0.438 seconds, Fetched: 1 row(s)

//指定日期和格式获取时间戳
hive (default)> SELECT UNIX_TIMESTAMP('2021-01-18','yyyy-mm-dd');
OK
_c0
1610899260
Time taken: 0.202 seconds, Fetched: 1 row(s)

//选取当前的时间
hive (default)> select current_timestamp;
OK
_c0
2021-10-18 11:26:18.576
Time taken: 0.167 seconds, Fetched: 1 row(s)

FROM_UNIXTIME:根据时间戳返回时间

查看函数的使用方法和含义:

hive (default)> desc function from_unixtime;
OK
tab_name
from_unixtime(unix_time, format) - returns unix_time in the specified format
Time taken: 0.026 seconds, Fetched: 1 row(s)

举例使用:

//根据时间戳和指定的格式返回时间
hive (default)> select from_unixtime(1610899260,'yyyy-mm-dd');
OK
_c0
2021-01-18
Time taken: 0.143 seconds, Fetched: 1 row(s)


//根据时间戳返回
hive (default)> select from_unixtime(1610899260);
OK
_c0
2021-01-18 00:01:00
Time taken: 0.163 seconds, Fetched: 1 row(s)

CURRENT_DATE:返回当前的时间

hive (default)> select current_date;
OK
_c0
2021-10-18
Time taken: 0.132 seconds, Fetched: 1 row(s)

TO_DATE:返回给定参数中的日期

hive (default)> select to_date('2021-01-12 12:13:45');
OK
_c0
2021-01-12
Time taken: 0.302 seconds, Fetched: 1 row(s)

YEAR:获取年

hive (default)> select YEAR('2021-01-12 12:13:45');
OK
_c0
2021
Time taken: 0.217 seconds, Fetched: 1 row(s

MONTH:获取月

hive (default)> select month('2021-01-12 12:13:45');
OK
_c0
1
Time taken: 0.165 seconds, Fetched: 1 row(s)

DAY:获取日期

hive (default)> select DAY('2021-01-12 12:13:45');
OK
_c0
12
Time taken: 0.289 seconds, Fetched: 1 row(s)

HOUR:获取小时

hive (default)> select hour('2021-01-12 12:13:45');
OK
_c0
12
Time taken: 0.071 seconds, Fetched: 1 row(s)

MINUTE:获取分组

hive (default)> select MINUTE('2021-01-12 12:13:45');
OK
_c0
13
Time taken: 0.123 seconds, Fetched: 1 row(s)

SECOND:获取秒

hive (default)> select SECOND('2021-01-12 12:13:45');
OK
_c0
45
Time taken: 0.08 seconds, Fetched: 1 row(s)

DAYOFMONTH:一个月中的第几天

hive (default)> select dayofmonth('2021-01-12 12:13:45');
OK
_c0
12
Time taken: 0.099 seconds, Fetched: 1 row(s)

//对于日期,如果选择是超过该月的最大天数,自动识别成下一个月
hive (default)> select dayofmonth('2021-01-35 12:13:45');
OK
_c0
4
Time taken: 0.067 seconds, Fetched: 1 row(s)

MONTHS_BETWEEN:两个日期间的月份

这个函数不常用

hive (default)> select months_between('2020-04-01','2020-10-28');
OK
_c0
-6.87096774
Time taken: 0.212 seconds, Fetched: 1 row(s)

MONTHS_ADD:日期加减

hive (default)> select add_months('2020-12-28',3);
OK
_c0
2021-03-28
Time taken: 0.095 seconds, Fetched: 1 row(s)


hive (default)> select add_months('2020-12-28',-3);
OK
_c0
2020-09-28
Time taken: 0.209 seconds, Fetched: 1 row(s)

DATEDIFF:日期相减,求相差的天数

hive (default)> select datediff('2020-10-12','2020-10-28');
OK
_c0
-16
Time taken: 0.164 seconds, Fetched: 1 row(s)

DATE_ADD:日期加天数

hive (default)> select date_add('2020-10-28',3);
OK
_c0
2020-10-31
Time taken: 0.469 seconds, Fetched: 1 row(s)

DATE_SUB:日期减天数

hive (default)> select date_sub('2020-10-28',3);
OK
_c0
2020-10-25
Time taken: 0.166 seconds, Fetched: 1 row(s)

//输入负数,相当于+3
hive (default)> select date_sub('2020-10-28',-3);
OK
_c0
2020-10-31
Time taken: 0.157 seconds, Fetched: 1 row(s)

LAST_DAY:当月的最后一天

hive (default)> select last_day('2021-04-12');
OK
_c0
2021-04-30
Time taken: 0.495 seconds, Fetched: 1 row(s)

//2月没有30号,选取了30号相当于就是往后推,到了三月份
hive (default)> select last_day('2020-02-30');
OK
_c0
2020-03-31
Time taken: 0.16 seconds, Fetched: 1 row(s)

DATE_FORMAT:日期格式化

hive (default)> select DATE_FORMAT('2021-10-28 12:12:12','YYYY-MM');
OK
_c0
2021-10
Time taken: 0.101 seconds, Fetched: 1 row(s)

hive (default)> SELECT DATE_FORMAT('2020-12-22 12:12:11','YYYY/MM/DD HH:MM:SS');
OK
_c0
2020/12/357 12:12:00
Time taken: 0.083 seconds, Fetched: 1 row(s)

ROUND():四舍五入

hive (default)> select round(3.14);
OK
_c0
3.0
Time taken: 0.174 seconds, Fetched: 1 row(s)

hive (default)> select round(3.14112222);
OK
_c0
3.0
Time taken: 0.133 seconds, Fetched: 1 row(s)

CEIL:向上取整

hive (default)> select ceil(2.3122);
OK
_c0
3
Time taken: 0.144 seconds, Fetched: 1 row(s)

hive (default)> select ceil(12.7322);
OK
_c0
13
Time taken: 0.087 seconds, Fetched: 1 row(s)

FLOOR:向下取整

hive (default)> select floor(12.7322);
OK
_c0
12
Time taken: 0.072 seconds, Fetched: 1 row(s)

UPPER:转大写

hive (default)> select upper('low');
OK
_c0
LOW
Time taken: 0.258 seconds, Fetched: 1 row(s)

LOWER:转小写

hive (default)> select lower('LOW');
OK
_c0
low
Time taken: 0.127 seconds, Fetched: 1 row(s)