CODEWITHSUNDEEP
hadoophivehiveql
dheee
dheee

Reputation: 1648

timestamp not working in hive

I have a table with one column data type as 'timestamp'. Whenever i try to do some queries on the table, even simple select statement i am getting errors.

example of a row in my column,

'2014-01-01 05:05:20.664592-08'

The statement i am trying,

'select * from mytable limit 10;'

the error i am getting is 

'Failed with exception java.io.IOException:java.lang.NumberFormatException: For input string: "051-08000"'

Date functions in hive like TO_DATE are also not working.If i change the data type to string, i am able to extract the date part using substring. But i need to work with timestamp.

Has anyone faced this error before? Please let me know.

Upvotes: 0

Views: 1084

Answers (1)

Jordan Young
Jordan Young

Reputation: 356

Hadoop is having trouble understanding '2014-01-01 05:05:20.664592-08' as a timestamp because of the "592-08" at the end. You should change the datatype to string, cut off the offending portion with a string function, then cast back to timestamp:

select cast(substring(time_stamp_field,1,23) as timestamp) from mytable

Upvotes: 1

Related Questions