2

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?

4

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

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;
0

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;
  • Thanks! It works after changing created - delta to delta only. – secdave Jul 24 '18 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.