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;

yearmonthminutesecond

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;

hourdayweekofyear

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()

currentdate_currenttimestamp

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.

 

8 comments

  1. I’ve been exploring for a little for any high-quality
    articles or blog posts on this sort of house . Exploring
    in Yahoo I ultimately stumbled upon this site. Reading this info So i’m
    satisfied to express that I have an incredibly just right uncanny feeling I discovered exactly
    what I needed. I most certainly will make certain to
    don?t forget this website and give it a glance regularly.

    Like

  2. Hi there! Do you know if they make any plugins to help with Search Engine Optimization? I’m trying to get my blog to rank
    for some targeted keywords but I’m not seeing very good results.
    If you know of any please share. Kudos!

    Like

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