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 |
+-------+