Hive Date Functions 5


HIVE Date Functions

from_unixtime:

This function converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a STRING that represents the TIMESTAMP of that moment in the current system time zone in the format of “1970-01-01 00:00:00”. The following example returns the current date including the time.

from_utc_timestamp

This function assumes that the string in the first expression is UTC and then, converts that  string to the time zone of the second expression. This function and the to_utc_timestamp function do timezone conversions. In the following example, t1 is a string.

to_utc_timestamp:

This function assumes that the string in the first expression is in the timezone that is specified in the second expression, and then converts the value to UTC format. This function and the from_utc_timestamp function do timezone conversions.

unix_timestamp :

This function converts the date to the specified date format and returns the number of seconds between the specified date and Unix epoch. If it fails, then it returns 0. The following example returns the value 1237487400

unix_timestamp() :

This function returns the number of seconds from the Unix epoch (1970-01-01 00:00:00 UTC) using the default time zone.

unix_timestamp( string date ) :

This function converts the date in format ‘yyyy-MM-dd HH:mm:ss’ into Unix timestamp. This will return the number of seconds between the specified date and the Unix epoch. If it fails, then it returns 0.

unix_timestamp( string date, string pattern ) :

This function converts the date to the specified date format and returns the number of seconds between the specified date and Unix epoch. If it fails, then it returns 0.

from_unixtime( bigint number_of_seconds  [, string format] ) :

The FROM_UNIX function converts the specified number of seconds from Unix epoch and returns the date in the format ‘yyyy-MM-dd HH:mm:ss’.

To_Date( string timestamp ) :

The TO_DATE function returns the date part of the timestamp in the format ‘yyyy-MM-dd’.

YEAR( string date ) :

The YEAR function returns the year part of the date.

MONTH( string date ) :

The MONTH function returns the month part of the date.

DAY( string date ), DAYOFMONTH( date ) :

The DAY or DAYOFMONTH function returns the day part of the date.

HOUR( string date ) :

The HOUR function returns the hour part of the date.

MINUTE( string date ) :

The MINUTE function returns the minute part of the timestamp.

SECOND( string date )

The SECOND function returns the second part of the timestamp.

WEEKOFYEAR( string date ) 

The WEEKOFYEAR function returns the week number of the date.

DATEDIFF( string date1, string date2 ) 

The DATEDIFF function returns the number of days between the two given dates.

DATE_ADD( string date, int days ) 

The DATE_ADD function adds the number of days to the specified date

DATE_SUB( string date, int days ) 

The DATE_SUB function subtracts the number of days to the specified date

DATE CONVERSIONS :

Convert MMddyyyy Format to Unixtime

Note: M Should be Capital Every time in MMddyyyy Format

 Convert MMM dd, yyyy  Format to Unixtime

Convert yyyy-MM-dd to Unix_timestamp

Example Use Case of Date Conversions

 


About Siva

Senior Hadoop developer with 4 years of experience in designing and architecture solutions for the Big Data domain and has been involved with several complex engagements. Technical strengths include Hadoop, YARN, Mapreduce, Hive, Sqoop, Flume, Pig, HBase, Phoenix, Oozie, Falcon, Kafka, Storm, Spark, MySQL and Java.


Leave a comment

Your email address will not be published. Required fields are marked *

5 thoughts on “Hive Date Functions


Review Comments
default image

I have attended Siva’s Spark and Scala training. He is good in presentation skills and explaining technical concepts easily to everyone in the group. He is having excellent real time experience and provided enough use cases to understand each concepts. Duration of the course and time management is awesome. Happy that I found a right person on time to learn Spark. Thanks Siva!!!

Dharmeswaran ETL / Hadoop Developer Spark Nov 2016 September 21, 2017

.