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

谁借莪１个温暖的怀抱￠ 2022-09-16 08:50 223阅读 0赞

### HIVE当中的日期功能函数 ###

*  UXIX\_TIMESTAMP:返回当前时间戳
 *  FROM\_UNIXTIME:根据时间戳返回时间
 *  CURRENT\_DATE:返回当前的时间
 *  TO\_DATE:返回给定参数中的日期
 *  YEAR:获取年
 *  MONTH:获取月
 *  DAY:获取日期
 *  HOUR:获取小时
 *  MINUTE:获取分组
 *  SECOND:获取秒
 *  DAYOFMONTH:一个月中的第几天
 *  MONTHS\_BETWEEN:两个日期间的月份
 *  MONTHS\_ADD:日期加减
 *  DATEDIFF:日期相减,求相差的天数
 *  DATE\_ADD:日期加天数
 *  DATE\_SUB:日期减天数
 *  LAST\_DAY:当月的最后一天
 *  DATE\_FORMAT:日期格式化
 *  ROUND():四舍五入
 *  CEIL:向上取整
 *  FLOOR:向下取整
 *  UPPER:转大写
 *  LOWER:转小写

# 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)