Presto- get timestamp difference

Question

I am new to PrestoDB and want to write a query which will compare two timestamps, the first row date will be compare with the immediate next date row and if the difference is greater than 15 mins, then it will print that row. I have written below query but while executing it is throwing the error: "unexpected parameter(timestamp with timezone) for function from_iso8601_timestamp".

SELECT mt.logical_name, mt.cable_name, mt.dt, mt.met_date,       
date_diff('second', from_iso8601_timestamp(met_date),                 
lag(from_iso8601_timestamp(met_date)) over (order by met_date))
FROM MyTable mt
where mt.dt = 20181117 and mt.cable_name = 'cable' and mt.logical_name ='ABCD0000008'
ORDER BY mt.met_date;

Till now have not put any filter criteria to print only those rows where the difference is greater than 15 minutes and I also want to add +10:00 with the timestamp while doing the comparison. Seeking some help in this regards. Any help will be appreciated.

Answer 1

The column met_date isn't in the format from_iso8601_timestamp is looking for.:

"2018-11-07 00:05:00" should be "2018-11-07T00:05:00".

As a quick fix, you could replace from_iso8601_timestamp(met_date) with from_iso8601_timestamp(replace(met_date, ' ', 'T'))

From your initial query provided, you can then select

SELECT
  logical_name, cable_name, date_add('minute', 10, met_date) as met_date, time_difference
FROM (

SELECT mt.logical_name, mt.cable_name, mt.dt, mt.met_date,       
  date_diff('second', met_date,                 
  lag(met_date) over (order by met_date)) AS 
time_difference
FROM (

  SELECT mt.logical_name, mt.cable_name, mt.dt, 
  from_iso8601_timestamp(replace(met_date, ' ', 'T')) as met_date
  FROM MyTable mt
  where mt.dt = 20181117 and mt.cable_name = 'cable' and mt.logical_name 
  ='ABCD0000008'

   )
)
WHERE time_difference >= 15
  ORDER BY met_date DESC