Date function in hive

Posted on by Sumit Kumar

1)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.

hive> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
OK                                        
20150518 05:43:37
Time taken: 0.153 seconds, Fetched: 1 row(s)
2)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.
hive> SELECT  from_utc_timestamp(‘1970-01-01 07:00:00’, ‘JST’);
OK
19700101 16:00:00
Time taken: 0.148 seconds, Fetched: 1 row(s)
3)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.
hive> SELECT to_utc_timestamp (‘1970-01-01 00:00:00’,‘America/Denver’);
OK
19700101 07:00:00
Time taken: 0.153 seconds, Fetched: 1 row(s)
4)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
hive> SELECT unix_timestamp (‘2009-03-20’, ‘yyyy-MM-dd’);
OK
1237487400
Time taken: 0.156 seconds, Fetched: 1 row(s)

5)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.

hive> select UNIX_TIMESTAMP(‘2000-01-01 00:00:00’);
OK
946665000
Time taken: 0.147 seconds, Fetched: 1 row(s)
6)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.
hive> select UNIX_TIMESTAMP(‘2000-01-01 10:20:30’,‘yyyy-MM-dd’);
OK
946665000
Time taken: 0.148 seconds, Fetched: 1 row(s)
7)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.
hive> select FROM_UNIXTIME( UNIX_TIMESTAMP() );

8)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’.

hive> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());

9)To_Date( string timestamp ) :

hive> select TO_DATE(‘2000-01-01 10:20:30’);
OK
20000101
10)WEEKOFYEAR( string date )
The WEEKOFYEAR function returns the week number of the date.
hive> SELECT WEEKOFYEAR(‘2000-03-01 10:20:30’);
OK
9
11)DATEDIFF( string date1, string date2 )
The DATEDIFF function returns the number of days between the two given dates.
hive> SELECT DATEDIFF(‘2000-03-01’, ‘2000-01-10’);
OK
51
Time taken: 0.156 seconds, Fetched: 1 row(s)
12)DATE_ADD( string date, int days )
The DATE_ADD function adds the number of days to the specified date
hive> SELECT DATE_ADD(‘2000-03-01’, 5);
OK
20000306
13)DATE_SUB( string date, int days )
The DATE_SUB function subtracts the number of days to the specified date
hive> SELECT DATE_SUB(‘2000-03-01’, 5);
OK
20000225

14)DATE CONVERSIONS :Convert MMddyyyy Format to Unixtime

Note: M Should be Capital Every time in MMddyyyy Format

select cast(substring(from_unixtime(unix_timestamp(dt, ‘MMddyyyy’)),1,10) as date) from sample;

About ATI Base Author: Sumit Kumar

Author of Hadoop tutorial View all posts by Sumit Kumar →

Got Something To Say:

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

*

*


Planet Polaris

Website Support
website support at Planet Polaris