Date functions in Apache Hive
hive> select current_date();
current_timestamp()
TO_DATE('2018-04-01 10:25:30')
YEAR('2018-03-01 11:20:50')
MONTH('2018-03-01 10:30:50')
DAY('2018-03-01 04:20:30')
DAYOFMONTH('2018-03-01 10:20:30')
HOUR('2018-03-01 10:20:30')
MINUTE('2018-03-01 12:20:30')
SECOND('2018-03-01 11:20:30')
WEEKOFYEAR('2018-05-01 6:20:30')
add_months(timestamp date, int months)
DATE_ADD('2018-03-01', 5)
select date_add(current_date(), 1);
DATE_SUB('2018-03-01', 5)
DATEDIFF('2018-03-01', '2018-02-10') -- difference in number of DAYS
UNIX_TIMESTAMP('2018-01-01 10:20:30','yyyy-MM-dd')
FROM_UNIXTIME( UNIX_TIMESTAMP() )
select year(from_unixtime(unixreviewtime)) as yr, count(*) as review_count from electronics group by year(from_unixtime(unixreviewtime));
SELECT from_unixtime(unix_timestamp('2013-01-01 10:10:10') + 10 * 60) AS result
from_utc_timestamp(timestamp, string timezone)
to_utc_timestamp(timestamp, string timezone)
SELECT (unix_timestamp('2017-10-10 10:10:10') - unix_timestamp('1970-01-01 00:00:00'))/60 -- difference in number of minutes
select unix_timestamp('17:22:38','HH:mm:ss') - unix_timestamp('14:53:19','HH:mm:ss'); -- difference in number of seconds
trunc('2015-03-17', 'MM')
select trunc(current_timestamp(), 'MONTH');
select extract(hour from "2016-10-20 05:06:07")
select extract(minute from interval '3 12:20:30' day to second)
select minute(CAST('2017-09-22 17:22:38' as timestamp) – CAST('2017-09-12 14:53:19' as timestamp ));
select CAST('2017-09-22' as DATE) - CAST('2017-09-12' as DATE );
Related Articles: Sqoop commands awscli commands
