Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Impala - Convert String to Timestamp

avatar
Explorer

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

1 ACCEPTED SOLUTION

avatar
[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.

View solution in original post

4 REPLIES 4

avatar
[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.

avatar
Explorer

Thanks for the solution.

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

 

avatar

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

avatar
Explorer

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.