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?