CODEWITHSUNDEEP
stringdateimpala
Pallavi
Pallavi

Reputation: 63

Convert date string with AM PM to 24 Hour timestamp in Impala

I am trying to convert a date string with AM/PM to timestamp in impala to check data conversion. My date string is as below:

10/07/2017 02:04:01.575000000 PM

I tried to convert this in Impala through below query:

select from_unixtime(unix_timestamp((Y_date), "MM/dd/yyyy HH:mm:ss.SSSSSSSSS 'ZZ'"), "yyyy-MM-dd HH:mm:ss.SSSSSS 'ZZ'") from table

The result I get is 2017-10-07 02:04:01.000000 .

I only lose the AM/PM however the hour part "02" is not getting converted to timestamp value "14". I need to get below as result:

2017-10-07 14:04:01.000000 .

I use Impala as my interface for querying Hadoop.

Any inputs would be helpful.

Thanks,

Vishu

Upvotes: 0

Views: 1714

Answers (1)

JimB
JimB

Reputation: 1

Haven't found a built in function for this. Gotta do an inefficient double query, adding 12 hours for PM:

SELECT cast(unix_timestamp(Y_date , "dd/MM/yyyy HH:mm:ss") + 43200 as timestamp) as 
FROM table_a
where instr(`datetime`,'PM') > 0 

union

SELECT cast(unix_timestamp(Y_date , "dd/MM/yyyy HH:mm:ss") as timestamp) as action_time 
FROM table_a
where instr(`datetime`,'AM') > 0

Upvotes: 0

Related Questions