The date time functions available in the Apache Hive are as follows. The most of them are also available in Apache Impala. The behavior of the functions in both Apache Hive and Impala would not vary.
Date Truncation in Apache Hive
This function returns the truncated date to the unit defined by the format. This will aim to return the first day of the specified month on the first day of the specified year.Supported formats: MONTH/MON/MM, YEAR/YYYY/YYY.Syntax: TRUNC(string date, string format)Examples: SELECT TRUNC(‘2020-04-26 13:04:05.000’, “MM”);This returns 2020-04-01You can use “MM” or “MONTH” or “MON”SELECT TRUNC(‘2020-04-26 13:04:05.000’, “YY”);This…
Identify Months Between Two Dates – Apache Hive & Impala
The function MONTHS_BETWEEN returns the number of months between the dates specified. If the first expression is later than the second, the outcome is positive, otherwise negative. This can usually be used in business situations, for example to assess whether a given number of months has elapsed or whether a certain end-of-month date has been…
Converting string into date – Apache Hive
TO_DATE function in Apache Hive helps in converting the string into DATE format. If the string has date and time, it will convert and return the date part.Syntax: to_date(string timestamp)Examples:SELECT TO_DATE(SUBSTR(‘ON 2020-04-01’, 4, 10));This returns 2020-04-01SELECT TO_DATE(‘2020-04-01 13:04:05.000’);This returns 2020-04-01Please click on the follow button to get updates on latest posts.
Formatting the date in Apache Hive
The “Date Format” function is used but not limited to the following situations. If the current or default date format needs to be changed to meet the legal and cultural criteria for the date and time of recognition of the region. If an output demands a formatted date. Syntax: date_format(date/timestamp/string ts, string fmt)Examples:SELECT date_format(‘2021-02-06’,”dd/MM/yyyy”);Returns 06/02/2021SELECT…
Comparison of two dates – Apache Hive & Apache Impala
There will be some situations where we need to compare two dates during the data analysis, to verify how many specified time intervals exist between them. The comparison can be to identify the number of days, weeks, and months between the dates specified. Note: The below specified functions are common in both Apache Hive &…
Adding and Substracting Months to a Date – Apache Hive & Apache Impala
We addressed in the previous post that “Date” is the most significant column that helps to explain patterns, trends and even business. From diagnostic analysis to prescriptive analysis and from descriptive analysis to prescriptive analysis, “Date/Time” plays a vital role. We will need to equate the data for the present date with the data for…
Adding and Substracting Days to a Date – Apache Hive & Impala
We addressed in the previous post that “Date” is the most significant column that helps to explain patterns, trends and even business. From diagnostic analysis to prescriptive analysis and from descriptive analysis to prescriptive analysis, “Date/Time” plays a vital role. We will need to equate the data for the present date with the data for…
Accessing Part of the Date – Apache Hive
The most critical and significant column is always the date/time in the data. A date/time dimension provides all the information you need for a given date and enables data to be processed as easily and reliably as possible. The date/time helps in understanding the patterns, trends and even business. It (Date/time) plays a critical role,…
Convert Multiple Date Formats into A single Format
Representation of the date and time varies between countries, various applications or application logs. When stored in a table form, we always use a single format to prevent misunderstanding. It is mandatory to be in a single format for querying and analytical purposes. If a dataset has a date column in different date-formats it needs…
Hive DateTime Functions – UNIX_TIMESTAMP() and FROM_UNIXTIME()
In this article, we will address the frequently utilized Hive date and time functions that convert the date to BIGINT and from BIGINT to the string or the desired date time format. UNIX_TIMESTAMP(): This function returns the current date and current date time in seconds. The return value will be in BIGINT. Note: This function…
Big Data and SQL 
2 comments