Apache Hive

Handle Date and Timestamp in HIVE like a pro – Everything you must know

1 Comment / Apache HIVE / By

Hive supports traditional UNIX timestamp data type with nanosecond upto 9 decimal precision (in Teradata it is till 6 decimal precision for timestamp data type). Typical TIMESTAMP data type has DATE part which is YEAR/MONTH/DAY and TIME part which is HOUR/MINUTE/SECOND/NanoSecond. We can represent these components as :

TIMESTAMP : 2017-11-29 14:53:50.724
S.No Part Format Output Comment
1 Year Y 2017 Prints the Year
2 Year y 2017 Prints the Year
3 Year YY 17 Prints the year in 2 digit
4 Year yy 17 Prints the year in 2 digit
6 Year YYYY 2017 Prints the Year
7 Month M 11 Prints the month
8 Month MM 11 Prints the month in 2 digits
9 Month MMM Nov Prints the month name in 3 char
10 Month MMMM November Prints the month with entire name
11 Day d 29 Prints the day
12 Day dd 29 Prints the day in 2 digits
13 Day E Wed Prints the day name
14 Day D 333 Prints the day # in  the year
15 Day F 5 Prints 5th Wed of the month Nov
16 Hour H 14 Prints HOUR in 24 hour format.
17 Hour h 2 Prints HOUR in 12 hour format
18 Minute m 53 Prints Minute
19 Minute mm 53 Prints Minute in 2 digits
20 Second s 50 Prints Second
21 Second ss 50 Prints Second in 2 digits
22 MilliSecond S 724 Prints MiliSecond to 3 digits
23 Era G AD Prints AD/BC information
24 Week w 48 Prints Week in Year
25 Week W 5 Prints Week in the month
26 Period a PM Prints AM/PM
27 Time Zone z UTC Prints Timezone name
28 Time Zone zzzz Coordinated Universal Time Prints Timezone full name
29 Time Zone Z +0000 Prints Timezone time diff

Quiz Time :

hive> SELECT DATE_FORMAT(‘2017-11-29 14:53:50.724′,’YYYY-MM-d HH:mm:ss.S’);
OK
2017-11-29 14:53:50.724

What is the output now ?

1) SELECT DATE_FORMAT(‘2017-11-29 00:53:50.724′,’YYY’);
2) SELECT DATE_FORMAT(‘2017-11-29 00:53:50.724′,’yY’);
3) SELECT DATE_FORMAT(‘2017-11-29 00:53:50.724′,’YYYYY’);
4) SELECT DATE_FORMAT(‘2017-11-29 00:53:50.724′,’MMMMM’);
5) SELECT DATE_FORMAT(‘2017-11-29 00:53:50.724′,’ddd’);
6) SELECT DATE_FORMAT(‘2017-11-29 00:53:50.724′,’mmm’);

Check answer below:

Output 1: 2017
Output 2: 20172017
Output 3: 02017
Output 4: November
Output 5: 029
Output 6: 053

Now you know how to handle & cast various DATE & Timestamp formats into desired formats.
In the next few posts we will see how to perform various operations on DATE TIMESTAMP data types in HIVE.

1 thought on “Handle Date and Timestamp in HIVE like a pro – Everything you must know”

Leave a Reply