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.

Error converting column: 10 TO TIMESTAMP (Data is: 2014-09-24 19:21:38:532+0000)-Impala

Error converting column: 10 TO TIMESTAMP (Data is: 2014-09-24 19:21:38:532+0000)-Impala

New Contributor

Hi,

we have the context as , A table containing the date column with STRING as data type in the format  2014-09-24 19:21:38:532+0000. Tableau  is taking a lot of time while doing operations on this date column. Impala does not support DATE data type to convert it to date though hive supports, so we changed to TIMESTAMP format but Impala returns the following error.

 

Error converting column: 10 TO TIMESTAMP (Data is: 2014-10-09 11:46:48:424+0000). Not sure if that is because the data is following the format as HH:MM:SEC:sssss. Any ideas on this how can we make this data better read through impala with out much performance issue.

 

 

Regards

Sree

3 REPLIES 3

Re: Error converting column: 10 TO TIMESTAMP (Data is: 2014-09-24 19:21:38:532+0000)-Impala

Master Collaborator

I'd suggest having a look at the builtin timestamp functions. The following should help in your situation, but the conversion may come with a performance hit - you'll need to try it:

 

select from_unixtime(unix_timestamp("2014-09-24 19:21:38:532+0000", "yyyy-MM-dd HH:mm:ss:SSS"));

 

Re: Error converting column: 10 TO TIMESTAMP (Data is: 2014-09-24 19:21:38:532+0000)-Impala

Explorer

Here the problem is not with the static data, it is about the column having such kind of data.

Highlighted

Re: Error converting column: 10 TO TIMESTAMP (Data is: 2014-09-24 19:21:38:532+0000)-Impala

Master Collaborator

I understand. Change my example to:

 

select from_unixtime(unix_timestamp(my_custom_string_column, "yyyy-MM-dd HH:mm:ss:SSS")) from mytable;

 

The second parameter describes the date format to be parsed.