What is a Hive Date Function?
Built-in Hive functions for manipulating date and timestamp data, enabling extraction, formatting, arithmetic, and conversions in SQL queries on Hadoop
Summary
- Functions include unix_timestamp, from_unixtime, to_date, date_add, date_sub, datediff, and date_format for comprehensive date manipulation in HiveQL queries
- Enables extraction of year, month, day, hour, minute, and second components, plus calculations like adding intervals or computing differences between timestamps
- Critical for time-series analysis, data partitioning by date, and temporal aggregations in data warehouses built on Hadoop ecosystems
What is a Hive Date Function?
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.
Gartner®: Databricks Cloud Database Leader
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 |