Apache Hive Date Time Functions

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

Continue reading

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

Continue reading

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-01 SELECT TO_DATE(‘2020-04-01 13:04:05.000’);This returns 2020-04-01 Please click on the follow button to get updates on latest posts.

Continue reading

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

Continue reading

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 &

Continue reading

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

Continue reading

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,

Continue reading

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

Continue reading


  1. Pingback: URL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s