hive日期函数的使用

隔壁老登
于 2024-09-05 20:53:21 发布
阅读量450
点赞数 2
分类专栏：大数据文章标签： hive hadoop 数据仓库
本文链接：https://blog.csdn.net/weixin_45547818/article/details/141940570
版权
大数据专栏收录该内容
19 篇文章 0 订阅
订阅专栏
hive日期函数的使用


hive日期函数如下:
current_date
current_timestamp
date_format
date_add
date_sub
datediff
day          
dayofmonth   
dayofweek    
floor_day     
floor_hour    
floor_minute  
floor_month   
floor_quarter 
floor_second  
floor_week    
floor_year    
from_unixtime
from_utc_timestamp
last_day
min             
minute          
month           
months_between  
next_day
to_date
to_unix_timestamp         
to_utc_timestamp          
unix_timestamp
weekofyear
year

查看函数用法:
desc function 'date_add';
+---------------------------------------------------------------------------------------+
|                                       tab_name                                        |
+---------------------------------------------------------------------------------------+
| date_add(start_date, num_days) - Returns the date that is num_days after start_date.  |
+---------------------------------------------------------------------------------------+

desc function extended 'date_add';
+--------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                  tab_name                                                                  |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| date_add(start_date, num_days) - Returns the date that is num_days after start_date.                                                       |
| start_date is a string in the format 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'. num_days is a number. The time part of start_date is ignored.  |
| Example:                                                                                                                                   |
|    > SELECT date_add('2009-07-30', 1) FROM src LIMIT 1;                                                                                    |
|   '2009-07-31'                                                                                                                             |
| Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFDateAdd                                                                     |
| Function type:BUILTIN                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------+

hive日期函数示例:
select current_date();	
+-------------+
|     _c0     |
+-------------+
| 2024-09-05  |
+-------------+


select current_timestamp();
+--------------------------+
|           _c0            |
+--------------------------+
| 2024-09-05 17:59:00.576  |
+--------------------------+

select date_format(current_timestamp(),'yyyyMMdd');
select date_format('2020-09-20','yyyyMMdd');
+-----------+
|    _c0    |
+-----------+
| 20240905  |
+-----------+

select date_add('2020-09-20',1);
+-------------+
|     _c0     |
+-------------+
| 2020-09-21  |
+-------------+

select date_sub('2020-09-20',1);
+-------------+
|     _c0     |
+-------------+
| 2020-09-19  |
+-------------+

select datediff('2020-09-20','2020-10-02');
+------+
| _c0  |
+------+
| -12  |
+------+


select day('2020-09-20');
+------+
| _c0  |
+------+
| 20   |
+------+

select dayofmonth('2020-09-20');
+------+
| _c0  |
+------+
| 20   |
+------+

select dayofweek('2020-09-20');
+------+
| _c0  |
+------+
| 1    |
+------+

select floor_day(CAST('2020-09-20 12:13:14' AS TIMESTAMP));
+------------------------+
|          _c0           |
+------------------------+
| 2020-09-20 00:00:00.0  |
+------------------------+

select floor_hour(CAST('2020-09-20 12:13:14' AS TIMESTAMP));
+------------------------+
|          _c0           |
+------------------------+
| 2020-09-20 12:00:00.0  |
+------------------------+

select floor_minute(CAST('2020-09-20 12:13:14' AS TIMESTAMP));
+------------------------+
|          _c0           |
+------------------------+
| 2020-09-20 12:13:00.0  |
+------------------------+

select floor_month(CAST('2020-09-20 12:13:14' AS TIMESTAMP));
+------------------------+
|          _c0           |
+------------------------+
| 2020-09-01 00:00:00.0  |
+------------------------+

select floor_quarter(CAST('2020-09-20 12:13:14' AS TIMESTAMP));
+------------------------+
|          _c0           |
+------------------------+
| 2020-07-01 00:00:00.0  |
+------------------------+

select floor_second(CAST('2020-09-20 12:13:14' AS TIMESTAMP));
+------------------------+
|          _c0           |
+------------------------+
| 2020-09-20 12:13:14.0  |
+------------------------+

select floor_week(CAST('2020-09-20 12:13:14' AS TIMESTAMP));
+------------------------+
|          _c0           |
+------------------------+
| 2020-09-14 00:00:00.0  |
+------------------------+

select floor_year(CAST('2020-09-20 12:13:14' AS TIMESTAMP));
+------------------------+
|          _c0           |
+------------------------+
| 2020-01-01 00:00:00.0  |
+------------------------+

select from_unixtime(1725530691);
+----------------------+
|         _c0          |
+----------------------+
| 2024-09-05 18:04:51  |
+----------------------+

select from_utc_timestamp(1725530691);
select last_day('2020-09-20');
+-------------+
|     _c0     |
+-------------+
| 2020-09-30  |
+-------------+

select minute('2020-09-20 12:13:14');
+------+
| _c0  |
+------+
| 13   |
+------+

select month('2020-09-20 12:13:14');
+------+
| _c0  |
+------+
| 9    |
+------+

select months_between('2020-09-20 12:13:14','2020-10-20 12:13:14');
+-------+
|  _c0  |
+-------+
| -1.0  |
+-------+

select next_day('2020-09-20 12:13:14','TU');
+-------------+
|     _c0     |
+-------------+
| 2020-09-22  |
+-------------+

select to_date('2020-09-20 12:13:14');
+-------------+
|     _c0     |
+-------------+
| 2020-09-20  |
+-------------+

select to_unix_timestamp('2020-09-20 12:13:14');
+-------------+
|     _c0     |
+-------------+
| 1600575194  |
+-------------+
select to_utc_timestamp('2020-09-20 12:13:14');

select unix_timestamp();
+-------------+
|     _c0     |
+-------------+
| 1725530691  |
+-------------+

select weekofyear('2020-09-20 12:13:14');
+------+
| _c0  |
+------+
| 38   |
+------+

select year('2020-09-20 12:13:14');
+-------+
|  _c0  |
+-------+
| 2020  |
+-------+