CODEWITHSUNDEEP
hadoophivehiveql
Harish
Harish

Reputation: 613

Hive query to Extract Date and Hour separately from String

I need to extract Date and hour from the string column in hive.

Table: enter image description here

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.

Upvotes: 2

Views: 18863

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

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 |
+------------+----+----+

Upvotes: 5

Related Questions