Conversion of timestamp/varchar column to bigint in presto which contains two different date formats

Question

I have a source table click with a column named click_date whose datatype is varchar, it contains values with two different format of dates in the same column. For example:

Jul 17 2018 4:54PM
2019-02-05 08:20:29.000

I have a target table named click and I need to map the data into it in a column named click_date whose datatype is bigint.

So it is throwing an error while doing casting

Tried the below :

td_time_parse(cast(cast(click_date as timestamp)as varchar)) 

But it doesn't solve both the formats.. I need to convert both the date formats into integer so that it can be loaded into the target.

note that target data type cannot be changed and it is bigint. Фny leads will be appreciated.

Answer 1

You could use a combination of date_parse and to_unixtime to convert your date in varchar into a unixtimestamp (which is in double datatype).

for date format: 2019-02-05 08:20:29.000

to_unixtime(date_parse('2019-02-05 08:20:29.000','%Y-%m-%d %h:%i:%s'))

for date format: Jul 17 2018

to_unixtime(date_parse('Jul 17 2018','%b %d %Y'))

This will return you a double datatype of your date which you can store it in your database.

Answer 2

You can use below query to handle both formats of dates with one expression. It essentially looks if the date/timestamp starts with a numeric or not.

select 
if(click_date rlike '^[0-9]', unix_timestamp(click_date ,'yyyy-MM-dd HH:mm:ss'), unix_timestamp(click_date,'MMM dd yyyy')) as click_date_mod 
from click;