Hive Date Function

Glossary Item
« Back to Glossary Index
Source Databricks

Hive provides many built-in functions to help us in the processing and querying of data. Some of the functionalities provided by these functions include string manipulation, date manipulation, type conversion, conditional operators, mathematical functions, and several others.

Types of Built-in Functions in HIVE

Date Functions – Mainly used to perform operations on date data types such as adding the number of days to the date or other similar operations
Mathematical Functions– these functions are primarily used to perform mathematical calculations.
Conditional Functions – These functions are used to test conditions and returns a value based on whether the test condition is true or false.
String Functions – these are used to perform operations on strings such as finding the length of a string etc.

Collection Functions – These functions are used to find the size of the complex types like array and map. There is one collection function and that is SIZE. The SIZE function’s main usage is to find the number of elements in an array and map.

Type Conversion Function – This function’s usage is to convert the data from one type to another. The only type conversion function is CAST.

Table Generating Functions: – These functions can be used to turn a single row into multiple rows. EXPLODE is the only table generated function. This function uses an array as an input and outputs the elements of the array into separate rows.

Date types are highly formatted; in their case, each date value contains the century, year, month, day, hour, minute, and second. These functions are used to perform operations on date data types like adding the number of days to the date, conversion of Date types from one type to another type etc.

Below are the most commonly used Hadoop Hive DateTime functions:

Function Name Return Type Description
Unix_Timestamp BigInt It will get current Unix timestamp in seconds.
To_date(string timestamp) String It will fetch and give the date part of a timestamp string:
year(string date) INT It will fetch and give the year part of a date or a timestamp string.
quarter (date/timestamp/string) INT The function quarter was introduced in Hive 1.3, and it will fetch and give the quarter of the year for a date, timestamp, or string in the range 1 to 4
month(string date) INT It will give the month part of a date or a timestamp string.
hour(string date) INT The hour function will fetch and gives the hour of the timestamp
minute(string date) INT This function will return  minute from the timestamp
Date_sub(string starting date, int days) string The DATE_SUB function subtracts the number of days to the specified date
Current_date date It will return the current date at the start of query evaluation.
LAST _day(string date) string It will fetch and give the last day of the month which the date belongs to
trunc(string date, string format) string This function strips off fields from a TIMESTAMP value
« Back to Glossary Index