Search code examples
sqltimestampunix-timestampimpala

Converting unformatted string timestamp value to Unix_Timestamp in Impala

I have a string timestamp column as below in one of the table.

Column  
20210108003444  ---- 8th Jan 2021 00.34 AM  
20201109013716      
2020112422811

How can I convert this into a unix_timestamp in Impala so that I would be able to use it for calculating time differences between 2 such values in minutes?

It gets tricky since the timestamp value in string does not have separator like - or /

Thank You.

Solution

  • Try unix_timestamp and provide a suitable date format:

    select unix_timestamp(column, 'yyyyMMddHHmmss') from mytable;

    See the docs for more information.