Impala - Convert String to Timestamp

[#1]

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

[#2 · Accepted solution]

[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#date.... You can see above how it might work.

[#3]

Thanks for the solution.

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

[#4]

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');

[#5]

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.