Converting unformatted string timestamp value to Unix

Ajinkya Pujari

2021-04-02T17:15:35

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.

Copyright License：
Author:「Ajinkya Pujari」,Reproduced under the CC 4.0 BY-SA copyright license with link to original source & disclaimer.
Link to：https://stackoverflow.com/questions/66917062/converting-unformatted-string-timestamp-value-to-unix-timestamp-in-impala

About “Converting unformatted string timestamp value to Unix_Timestamp in Impala” questions

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_timest...

sqoop date to impala timestamp

I am importing data with sqoop from MySQL to hdfs as parquet file where it is used by Impala. There is a problem with conversion of MySQL DATE type to Impala TIMESTAMP. Impala error message when

Convert the concatenated string to timestamp in Impala

I have a column which is concatenation of two columns i.e, date and timestamp (both are STRING type). Now I want to convert this new column to timestamp in IMPALA | New_col | |---------------...

Convert date from string to timestamp in impala

I know this question has already been asked but my input is different. Also I have not found any documentation on this. I have to convert a string to timestamp in Impala, and I am doing like- se...

Impala is converting time into GMT how to avoid that

I'm casting a datefeild into time stamp in hive as from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as timestamp) as column_name, when I query it through impala time is displaying in GMT form...

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 thr...

Impala convert timestamp AM/PM

I have table in hive with string in 12 hrs format in which data is appended every day. I don't have control over hive table. Need to expose as view in impala after conversion. to_timestamp() doesn't

Converting Epoch Time in String to date field in Impala SQL

i have an epoch date time field which is stored as string in database table. i need to cast this to timestamp in impala sql. I am using the below format and not working. cast(unix_timestamp(

Convert seconds (string) to timestamp in Impala

I have 2 columns: end_time and seconds end_time is stored as a unix_timestamp and seconds is stored as a string. I need to subtract the end_time column from the seconds column to get a new column ...

Impala built-in function: unix_timestamp function is not accurate enough

I am using the following impala query to extract data from my_table: select id, timestamp, unix_timestamp(timestamp) as ts from my_table The output is: id timestamp ...