question Re: Impala - Convert String to Timestamp in Support Questions

Impala - Convert String to Timestamp

RNN — Wed, 23 Oct 2019 20:18:51 GMT

Hi Team,

I have an impala table column under this format, 2019-Oct-14 20:00:01.027898 as string but I want to insert in to another table as timestamp. I tried so many ways but its giving me null.

Could you please guide me the easiest way to store this formatted string as timestamp in impala.

Thanks,
RNN

Re: Impala - Convert String to Timestamp

Tim Armstrong — Wed, 23 Oct 2019 21:10:59 GMT

[localhost:21000] default> select to_timestamp('2019-Oct-14 20:00:01.027898', 'yyyy-MMM-dd HH:mm:ss.SSSSSS'); Query: select to_timestamp('2019-Oct-14 20:00:01.027898', 'yyyy-MMM-dd HH:mm:ss.SSSSSS') Query submitted at: 2019-10-23 14:08:19 (Coordinator: http://tarmstrong-box:25000) Query progress can be monitored at: http://tarmstrong-box:25000/query_plan?query_id=0d4bd87f063c53a2:c8c5759b00000000 +----------------------------------------------------------------------------+ | to_timestamp('2019-oct-14 20:00:01.027898', 'yyyy-mmm-dd hh:mm:ss.ssssss') | +----------------------------------------------------------------------------+ | 2019-10-14 20:00:01.027898000 | +----------------------------------------------------------------------------+ Fetched 1 row(s) in 0.11s [localhost:21000] default>

The default timestamp format accepted by Impala is ISO 8601 - https://en.wikipedia.org/wiki/ISO_8601

to_timestamp() lets you specify a format string if you want more flexibility about input timestamp formats - https://docs.cloudera.com/documentation/enterprise/latest/topics/impala_datetime_functions.html#datetime_functions__to_timestamp. You can see above how it might work.

Re: Impala - Convert String to Timestamp

RNN — Wed, 23 Oct 2019 21:38:03 GMT

Thanks for the solution.

Could you please guide me for hive too, converting string to timestamp for the same formatted string.

Re: Impala - Convert String to Timestamp

Tim Armstrong — Wed, 23 Oct 2019 21:51:19 GMT

I'm less familiar with hive, but I think you have to do something like:

select date_format(UNIX_TIMESTAMP('2019-Oct-14 20:00:01.027898', 'yyyy-MMM-dd HH:mm:ss.SSSSSS'), 'yyyy-MM-dd HH:mm:ss.SSSSSS');

Re: Impala - Convert String to Timestamp

RNN — Wed, 23 Oct 2019 22:55:26 GMT

Thanks for your reply, but it didn't work.

I tried using other functions but it brings up the date time without milliseconds. But I want the milliseconds as well.