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.

Impala - Convert String to Timestamp

Solved Go to solution

Impala - Convert String to Timestamp

New Contributor

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

Accepted Solutions
Highlighted

Re: Impala - Convert String to Timestamp

Master Collaborator
[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.

4 REPLIES 4
Highlighted

Re: Impala - Convert String to Timestamp

Master Collaborator
[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.

Re: Impala - Convert String to Timestamp

New Contributor

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

Master Collaborator

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

New Contributor

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.

Don't have an account?
Coming from Hortonworks? Activate your account here