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