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, from diagnostic analysis to prescriptive analysis, from descriptive analysis to prescriptive analysis.

The most frequently mentioned date-time characteristics are: seconds, minute, hour, day, week, month, quarter and year.

The below date/time functions help in returning specific date part from a date column.

For test data, use the following commands in Apache Impala.
CREATE TABLE datetest(date1 TIMESTAMP, date2 TIMESTAMP);
INSERT INTO datetest VALUES(‘2021-01-23 19:14:20.000′,’2020-11-23 15:14:11.000’);

YEAR(string date):
Year function returns the year from the given input.
Example: SELECT YEAR(date1), YEAR(date2) FROM DateTest;

MONTH(string date):
Month function returns the month of the given input.
Example: SELECT MONTH(date1), MONTH(date2) FROM DateTest;

MINUTE(string date):
Minute function returns the minute of the given input.
Example: SELECT MINUTE(date1), MINUTE(date2) FROM DateTest;

SECOND(string date):
Second function returns the second of the given input.
Example: SELECT SECOND(date1), SECOND(date2) FROM DateTest;

DAY(string date) dayofmonth(date)
Day function returns the day of the given input.
Example: SELECT day(date1), day(date2) FROM DateTest;

DayOfMonth(date)
DayofMonth function returns the day of the given input.
Example: SELECT dayofmonth(date1), dayofmonth(date2) FROM DateTest;

HOUR(string date)
Second function returns the second of the given input.
Example: SELECT hour(date1), hour(date2) FROM DateTest;

WeekofYear(string date)
Second function returns the second of the given input.
Example: SELECT weekofyear(date1), weekofyear(date2) FROM DateTest;

Current_Date:
This function returns the current date (without timestamp).
Example: 
SELECT CURRENT_DATE()

Current_Timestamp:
This function returns the current date and time.
Example:
SELECT CURRENT_TIMESTAMP()

Extract(field FROM source)
As per the documentation, this function helps in retrieving fields such as days or hours from source. Source must be a date, timestamp, interval or a string that can be converted into either a date or timestamp. Supported fields include: day, dayofweek, hour, minute, month, quarter, second, week and year. This function introduced in Hive 2.2.0

Examples: 
SELECT EXTRACT(month F “2016-10-20”) results in 10.
SELECT EXTRACT(hour from “2016-10-20 05:06:07”) results in 5.
SELECT EXTRACT(dayofweek from “2016-10-20 05:06:07”) results in 5.
SELECT EXTRACT(month from interval ‘1-3’ year to month) results in 3.
SELECT EXTRACT(minute from interval ‘3 12:20:30’ day to second) results in 20.

QUARTER(date/timestamp/string) :
This returns the quarter of given input. This is added in Hive version 1.3.0

Examples:
SELECT quarter(date1), quarter(date2) FROM datetest;
Result: 1 and 4

Hope you liked this post.

Please click on the follow button to receive updates on latest posts.