Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

how to store time datatype in hive

how to store time datatype in hive

New Contributor

Receive data with location open and close hour as below. Unable to

location,DOW,openhour,closehour
abc,mon,0900,1630
abc,tue,0900,1630
abc,wed,0900,1700
abc,thu,0900,1700
abc,fri,0900,1700
abc,sat,0930,1300
abc,sun,0000,1000

Only option to store openhour and closehour as string. Making arithmetic operation on these columns complex.

Hive do not have time datatype. Is there an option to create a custom datatype to handle this

select cast('0130' as time) from test_table;
Error: Error while compiling statement: FAILED: ParseException line 1:22 cannot recognize input near 'time' ')' 'from' in primitive type specification (state=42000,code=40000)

1 REPLY 1
Highlighted

Re: how to store time datatype in hive

@Nagarajan Jayaraman

Hive supports time in form of timestamp, hence, the error as "time" is not recognized. If you are to hold the 0930 alone as openhour, then apt datatype would be string.

In case, you could hold data like "2018-10-15 09:30:00" as openhour, then you could use timestamp as datatype and select query would be like "select cast("2018-10-15 09:30:00" as timestamp) from test_table.

FYI, more details of Timestamp datatype.

Don't have an account?
Coming from Hortonworks? Activate your account here