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

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.NoPartFormatOutputComment
1YearY2017Prints the Year
2Yeary2017Prints the Year
3YearYY17Prints the year in 2 digit
4Yearyy17Prints the year in 2 digit
6YearYYYY2017Prints the Year
7MonthM11Prints the month
8MonthMM11Prints the month in 2 digits
9MonthMMMNovPrints the month name in 3 char
10MonthMMMMNovemberPrints the month with entire name
11Dayd29Prints the day
12Daydd29Prints the day in 2 digits
13DayEWedPrints the day name
14DayD333Prints the day # in  the year
15DayF5Prints 5th Wed of the month Nov
16HourH14Prints HOUR in 24 hour format.
17Hourh2Prints HOUR in 12 hour format
18Minutem53Prints Minute
19Minutemm53Prints Minute in 2 digits
20Seconds50Prints Second
21Secondss50Prints Second in 2 digits
22MilliSecondS724Prints MiliSecond to 3 digits
23EraGADPrints AD/BC information
24Weekw48Prints Week in Year
25WeekW5Prints Week in the month
26PeriodaPMPrints AM/PM
27Time ZonezUTCPrints Timezone name
28Time ZonezzzzCoordinated Universal TimePrints Timezone full name
29Time ZoneZ+0000Prints 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.

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

Leave a Reply

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