预先保存时间戳差异 [英] Presto- get timestamp difference

问题描述

我是PrestoDB的新手，想编写一个查询来比较两个时间戳，第一行的日期将与紧接的下一个日期的行进行比较，如果相差大于15分钟，则将打印该行.我在下面编写了查询，但是在执行查询时会抛出错误:函数from_iso8601_timestamp的意外参数(带时区的时间戳)".

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;

到现在为止，还没有放置任何过滤条件来仅打印差异大于15分钟的那些行，并且我还想在进行比较时为时间戳添加+10:00.在这方面寻求帮助.任何帮助将不胜感激.

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.

推荐答案

met_date列的格式不是from_iso8601_timestamp寻找的格式.

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

"2018-11-07 00:05:00"应为"2018-11-07T00:05:00".

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

作为快速解决方案，您可以将from_iso8601_timestamp(met_date)替换为from_iso8601_timestamp(replace(met_date，''，'T'))

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

这篇关于预先保存时间戳差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！