Convert seconds (string) to timestamp in Impala

Question

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 (i.e. start_time)

I'm having a hard time with this as I don't think Impala supports CONVERT() or DATEADD() for me to convert seconds to a timestamp, then to subtract it from end_time. Any suggestions on how to do this in Impala?

Example:

end_time              seconds
2020-09-02 21:12:34      65

Desired outcome:

start_time
2020-09-02 21:11:29

EDIT Progress so far:

CAST(CONCAT(CAST(from_timestamp(CAST(end_time AS TIMESTAMP),'yyyy-MM-dd') AS STRING), ' ', CAST(to_timestamp(from_timestamp(CAST(seconds AS TIMESTAMP),'HH:mm:ss'),'HH:mm:ss') AS STRING)) as TIMESTAMP) AS updated_time

The above gives me the seconds column in a TIMESTAMP format with the yyyy-MM-dd of the end_time.

How can I now subtract end_time and updated_time without messing up the dates?

E.g. I don't want to subtract 2021-01-01 from 2021-01-01. I want the time difference to come from the HH:mm:ss, not the yyyy-MM-dd

Answer 1

Actually a quick and simple solution in the end:

to_timestamp(end_time, 'yyyy-MM-dd HH:mm:ss') end_time
CAST(table.seconds AS INT) sec
date_sub(end_time, INTERVAL sec seconds) start_time