Hive query to Extract Date and Hour separately from String
15,859
I've change the data sample to something more reasonable
with dates as (select explode(array('1/11/16 3:29','12/7/16 17:19')) as dates)
select from_unixtime(unix_timestamp(dates,'dd/MM/yy HH:mm'),'yyyy-MM-dd') as the_date
,from_unixtime(unix_timestamp(dates,'dd/MM/yy HH:mm'),'H') as H
,from_unixtime(unix_timestamp(dates,'dd/MM/yy HH:mm'),'HH') as HH
from dates
+------------+----+----+
| the_date | h | hh |
+------------+----+----+
| 2016-11-01 | 3 | 03 |
| 2016-07-12 | 17 | 17 |
+------------+----+----+
Author by
Harish
Updated on June 04, 2022Comments
-
Harish almost 2 years
I need to extract Date and hour from the string column in hive.
select TO_DATE(from_unixtime(UNIX_TIMESTAMP(dates,'dd/MM/yyyy'))) from dates; output: 0016-01-01 0016-01-01 select TO_DATE(from_unixtime(UNIX_TIMESTAMP(dates,'hh'))) from dates; output: 1970-01-01 1970-01-01
Please advise how to take date seperately and hour seperately from the table column.