Hive Date Functions

Date Functions

1.from_unixtime(bigint unixtime[, string format])
select from_unixtime(1237519801, 'yyyy-MM-dd HH:mm:ss');
+----------------------+--+
|         _c0          |
+----------------------+--+
| 2009-03-20 11:30:01  |
+----------------------+--+

2.unix_timestamp()
select unix_timestamp();
+-------------+--+
|     _c0     |
+-------------+--+
| 1653980803  |
+-------------+--+

3.unix_timestamp(string date)
select unix_timestamp('1970-03-20 11:10:10');
+----------+--+
|   _c0    |
+----------+--+
| 6750610  |
+----------+--+

4.unix_timestamp(string date, string pattern)
select unix_timestamp('1970-03-20', 'yyyy-MM-dd');
+----------+--+
|   _c0    |
+----------+--+
| 6710400  |
+----------+--+

5.to_date(string timestamp)
select to_date("1970-01-01 00:00:00");
+-------------+--+
|     _c0     |
+-------------+--+
| 1970-01-01  |
+-------------+--+

6.year(string date)
select year("1970-01-01");
+-------+--+
|  _c0  |
+-------+--+
| 1970  |
+-------+--+

7.quarter(date/timestamp/string)
--1.3.0版本可用

8.month(string date)
select month('2020-01-01')
+------+--+
| _c0  |
+------+--+
| 1    |
+------+--+

9.day(string date) dayofmonth(date)
select day('2015-01-01');
+------+--+
| _c0  |
+------+--+
| 1    |
+------+--+
select dayofmonth('2015-01-01');
+------+--+
| _c0  |
+------+--+
| 1    |
+------+--+

10.hour(string date)
select hour('2022-02-02 11:01:02');
+------+--+
| _c0  |
+------+--+
| 11   |
+------+--+

11.minute(string date)
select minute("2022-01-01 12:09:09");
+------+--+
| _c0  |
+------+--+
| 9    |
+------+--+

12.second(string date)
select second("2022-01-01 12:09:09");
+------+--+
| _c0  |
+------+--+
| 9    |
+------+--+

13.weekofyear(string date)
select weekofyear('2022-05-31');
+------+--+
| _c0  |
+------+--+
| 22   |
+------+--+

14.extract(field FROM source)
select extract(month from '2022-05-31');
--2.2.0版本可用

15.datediff(string enddate, string startdate)
select datediff('2022-05-31','2021-06-25');
+------+--+
| _c0  |
+------+--+
| 340  |
+------+--+

16.date_add(date/timestamp/string startdate, tinyint/smallint/int days)
select date_add("2022-09-09",90);
+-------------+--+
|     _c0     |
+-------------+--+
| 2022-12-08  |
+-------------+--+

17.date_sub(date/timestamp/string startdate, tinyint/smallint/int days)
select date_sub('2022-05-31',1);
+-------------+--+
|     _c0     |
+-------------+--+
| 2022-05-30  |
+-------------+--+

18.from_utc_timestamp({any primitive type} ts, string timezone)
select from_utc_timestamp(2592000000,'UTF');
+------------------------+--+
|          _c0           |
+------------------------+--+
| 1970-01-31 08:00:00.0  |
+------------------------+--+

19.to_utc_timestamp({any primitive type} ts, string timezone)
select to_utc_timestamp(2592000.0,'PST');
+------------------------+--+
|          _c0           |
+------------------------+--+
| 1970-01-31 16:00:00.0  |
+------------------------+--+

20.current_date
--1.2.0版本可用

21.current_timestamp
--1.2.0版本可用

22.add_months(string start_date, int num_months, output_date_format)
select ad_months("2022-05-31",9,"yyyy-MM-dd");
--4.0.0版本可用

23.last_day(string date)
select last_day("2022-05-12");
+-------------+--+
|     _c0     |
+-------------+--+
| 2022-05-31  |
+-------------+--+

24.next_day(string start_date, string day_of_week)
--1.2.0版本可用

25.trunc(string date, string format)
--1.2.0版本可用

26.months_between(date1, date2)
 Example: months_between('1997-02-28 10:30:00', '1996-10-30') = 3.94959677
--1.2.0版本可用

27.date_format(date/timestamp/string ts, string fmt)
Example: date_format('2015-04-08', 'y') = '2015'
--1.2.0版本可用