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?