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

By RajApache HIVE1 comment

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.

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

Leave a Reply