Filter on time difference between current and next row

Question

I have a database table with the following structure:

id     | created
-------+---------------
1      | 2018-07-23 00:01:00
2      | 2018-07-23 00:02:00
...

Now I'd like to return all rows where the time difference to the next row is greater than e.g. 10 seconds.

I've tried with the Postgres window function, but couldn't get it to work, e.g.

SELECT
  created, 
  created - lag(created, 1) OVER (ORDER BY created) delta
FROM tablename
where created - lag(created, 1)  OVER (ORDER BY created) > interval '10 seconds'
ORDER BY created;

The error I get is that I'm not allowed to use window functions in the WHERE clause. How to get this right?

Answer 1

Consider the sequence of events in a SELECT query, as explained here:

• Best way to get result count before LIMIT was applied

Window functions operate on the result set after WHERE clauses have been applied. It would be a logical contradiction to have a window functions in a WHERE clause. Like mustaccio said: use a subquery - or a CTE - to filter on the result of a window function.

But also get the logic right:

where the time difference to the next row is greater than e.g. 10 seconds.

SELECT *
FROM  (
   SELECT created
        , lead(created) OVER (ORDER BY created) - created AS delta
   FROM   tbl
   ) t
WHERE  delta > interval '10 sec'
ORDER  BY created;

Answer 2

Use a subselect:

SELECT created, delta
FROM (
  SELECT
    created, 
    created - lag(created, 1) OVER (ORDER BY created) delta
  FROM tablename
) t
where created - delta > interval '10 seconds'
ORDER BY created;