Hive: Date functions

 

Below table summarizes the different date functions supported in Hive.

 

Function	Description	Example
current_timestamp()	Return current timestamp of the system.	SELECT current_timestamp();
day(string date)	Extract day information from date.	SELECT day('2021-02-05 05:37:23');
dayofmonth(string date)	Extract day information from date.	SELECT dayofmonth('2021-02-05 05:37:23');
datediff(string date1, string date2)	Return difference between two dates	SELECT datediff('2021-01-12', '2021-05-12');
date_add(string date, int noOfDays)	Add number of days to given date	SELECT date_add('2021-05-12', 5);
date_sub(string date1, int noOfDays)	Subtract number of days from given date.	SELECT date_sub('2021-05-12', 5);
from_unixtime(bigint number_of_seconds [, string format])	Convert epoch seconds to the date in given format.	SELECT from_unixtime(1649767043);   SELECT from_unixtime(1649767043, 'yyyy-MM-dd');
hour(string date)	Return hour part of date.	SELECT hour('2021-02-05 06:37:23');
minute(string date)	Return minute part of date	SELECT minute('2021-02-05 06:37:23');
month(string date)	Return month part of date	SELECT month('2021-02-05 05:37:23');
second(string date)	Return second part of date	SELECT second('2021-02-05 06:37:23');
to_date(string timestamp)	Extract date.	SELECT to_date('2021-02-05 05:37:23');
unix_timestamp() unix_timestamp( string date ) unix_timestamp( string date, string pattern )	Return number of seconds from the Unix epoch (1970-01-01 00:00:00 UTC)	SELECT unix_timestamp(); SELECT unix_timestamp('2022-01-01 00:00:00'); SELECT unix_timestamp('2022-01-01', 'yyyy-MM-dd');
weekofyear(string date)	Return week number of the date	SELECT weekofyear('2021-01-12');
year(string date)	Extract year part of the date	SELECT year('2021-02-05 05:37:23');

 

 

Previous                                                    Next                                                    Home