In SQL How to convert time into UNIX timestamp

In hive there is some data I have. Now I want to convert the start_timestamp into unix_timestamp in second. How to do that? Because the start_timestamp has two formats:

First format:

    2018-03-22 02:54:35

Second format:

    May 15 2018  5:15PM

Answer

First format is 'yyyy-MM-dd HH:mm:ss', second is 'MMM dd yyyy hh:mm:aa'. If the format is wrong, unix_timestamp function will return NULL. Try to convert using one format, if NULL, try to convert using the other format. This can be done using coalesce function:

select 
      coalesce(unix_timestamp(start_timestamp ,'yyyy-MM-dd HH:mm:ss'),
               unix_timestamp(start_timestamp ,'MMM dd yyyy hh:mm:aa')
              ) as UnixTimestamp
  from my_table;

Use from_unixtime() to convert it back to given format if necessary, like in this answer.

See patterns examples here: SimpleDateFormat