How to find out duration using 2 date value which are in string format in presto SQL

Question

Timestampdiff(hour,STR_TO_DATE(start_date, '%Y-%m-%d %H:%i:%s'),
STR_TO_DATE(target_date, '%Y-%m-%d %H:%i:%s')) as duration hours

I have tried to get duration using start date and target date which are in string format. Need to convert it into date format, need create new column for duration in hours and duration in days ....here duration means difference between start date and Target date.in presto SQL.

Answer 1

Also in Presto you should be able to subtract a timestamp from another, bigger, timestamp, and get an interval literal:

WITH
-- some input ...
indata(start_ts,target_ts) AS (
          SELECT TIMESTAMP '2024-03-04 08:00:00',TIMESTAMP '2024-03-06 09:00:00'
UNION ALL SELECT TIMESTAMP '2024-03-05 10:00:00',TIMESTAMP '2024-03-06 11:00:00'
)
SELECT
  start_ts
, target_ts
, target_ts - start_ts AS duration
FROM indata;

start_ts	target_ts	duration
2024-03-04 08:00:00	2024-03-06 09:00:00	2 01:00:00.000000
2024-03-05 10:00:00	2024-03-06 11:00:00	1 01:00:00.000000