0

I have a column which has values in string type like below:

31-Oct-2016 12:00 AM
31-May-2015 12:00 PM

I want to convert the above column values to timestamp in IMPALA. Tried with cast, to_timestamp and other ways , but it is either showing syntax error or Null as result. Can you please suggest a solution

2nd Requirement

There is a column like below in string, I want it to be converted to timestamp alone.

31-Oct-2016 12:00 
31-May-2015 12:00

please suggest a way, I'm new to Impala

Thanks in advance

Shanti
  • 13
  • 4

1 Answers1

0

You can use below code. Unfortunately, impala doesn't have am pm conversion capability, but you can use a little code to identify PM and add 12 hours to that to convert properly.

select 
if (right('31-Oct-2016 02:09 PM',2)='PM',
to_timestamp('31-Oct-2016 02:09 PM','d-MMM-yyyy H:m') + interval 12 hours,
to_timestamp('31-Oct-2016 02:09 PM','d-MMM-yyyy H:m')
) ampm_timestamp

Second requirement -
Impala always expects 24hour hour format when it converts datetime. So, in your case for 12 AM scenario, we have to do some special logic like below.
First check if its 12 AM, then minus 12 hours, else check if its PM, then add 12 hours(which covers 12PM scenario) and finally if its any other AM, it simply converts to timestamp.

select 
CASE WHEN right('31-Oct-2016 12:09 AM',2)='AM' AND RIGHT( SPLIT_PART('31-Oct-2016 12:09 AM',':',1),2)='12'
THEN to_timestamp('31-Oct-2016 12:09 AM','d-MMM-yyyy HH:mm') - interval 12 HOURS 
ELSE CASE WHEN  right('31-Oct-2016 12:09 AM',2)='PM'
THEN to_timestamp('31-Oct-2016 12:09 AM','d-MMM-yyyy HH:mm') + interval 12 HOURS 
ELSE to_timestamp('31-Oct-2016 12:09 AM','d-MMM-yyyy HH:mm') 
END END AMPM_TIMESTAMP
Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
  • Thank you for the above answer , but i'm looking for the exact time i've provided in the requirement like 12:00 AM or 12:00 PM, @Koushik Roy – Shanti Nov 22 '21 at 06:36