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”
you skipped nanoseconds representation !!!!