CODEWITHSUNDEEP
datehivehiveql
Surendaran M
Surendaran M

Reputation: 25

Split the date and save the date, month, year in different columns

input 

10-01-2019    

20-02-2019

22-03-2019

output

Date     Month              Year 

10       January            2019    

20       February           2019

30       March              2019

Upvotes: 1

Views: 465

Answers (3)

leftjoin
leftjoin

Reputation: 38290

Using split():

with your_data as(
select stack(3,'10-01-2019',   
               '20-02-2019',
               '22-03-2019'
        ) as dt
) --use your table instead of this

select dt[0] as day,
       dt[1] as month,
       dt[2] as year
from ( select split(dt,'-') as dt from your_data )s;

Result:

OK
day     month   year
10      01      2019
20      02      2019
22      03      2019
Time taken: 0.081 seconds, Fetched: 3 row(s)

Upvotes: 2

notNull
notNull

Reputation: 31480

We need to use from_unixtime and unix_timestamp functions to parse the date.

Then split the field in subquery and extract the date,month,year..

Example:

hive> select dt[0] day,dt[1] month,dt[2] year from( 
            select split(from_unixtime(unix_timestamp("10-01-2019",'dd-MM-yyyy'),'dd-MMMM-yyyy'),'-')dt
)e;

Result:

day     month   year
10      January 2019

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Try this

with t as ( select  unix_timestamp('10-01-2019' , 'dd-MM-yyyy') as dt )
select from_unixtime(dt,'dd')  as Date,
       from_unixtime(dt,'MMMM')  as Month,
       from_unixtime(dt,'YYYY')  as Year
 from t;

Result

Total MapReduce CPU Time Spent: 2 seconds 720 msec
OK
10  January 2019
Time taken: 23.206 seconds, Fetched: 1 row(s)

Upvotes: 0

Related Questions