Как получить необходимые промежутки времени?

Question

[Alexey Bille]

БД - timescaledb/postgresql

Есть таблица с полями:
- id
- timestamp

В этой таблице примерно раз в минуту появляется запись, мне нужно найти разрывы, допустим в таблице есть записи

- 2023.01.01 00:00:00
- 2023.01.01 00:01:00
- 2023.01.01 00:02:00
- 2023.01.01 00:03:00
...
- 2023.01.01 00:44:00
- 2023.01.01 00:45:00
- 2023.01.01 01:00:00
- 2023.01.01 01:01:00
...
- 2023.01.01 01:29:00
- 2023.01.01 01:30:00

В результате запроса должен получить данные вида:

2023.01.01 00:00:00 | 2023.01.01 00:45:00
2023.01.01 00:01:00 | 2023.01.01 01:30:00

Comments

[Модератор]

На вопрос «как сделать» отвечает документация и поиск в интернет.

Тут отвечают на вопросы «почему я сделал, как в документации, а оно не работает. Поискал в интернет, вот запросы, в ответах не нашел. Что я делаю не так?»

Покажите, как вы пробовали решить проблему, приведите код попытки (пусть неудачной), опишите, как запускали, что ожидали и что получилось.
За готовыми решениями - на фриланс. В текущем виде это не вопрос, а задание. Нарушен п.5.12 Регламента.

[Алан Гибизов]

А как вы пробовали и какие проблемы?

[Alexey Bille]

Проблема в том что я не понимаю возможно ли подобный запрос сформировать в SQL

В timescale есть time_bucket - https://docs.timescale.com/use-timescale/latest/ti...

Но он группирует по заранее известным промежуткам, такой вариант мне не подойдет

[Alexey Bille]

Как вариант вижу что можно попробовать повешать триггер на создание записи в таблице, в этот момент в другой таблице в которой будет 2 поля (start, end) проверять есть ли запись где end - timestamp <= 1 минуты, то изменять запись и ставить end=timestamp, в противном случае просто создавать новую запись где start=timestamp, end=timestamp, но мне кажется что есть более простые решения без создания еще одной таблицы и работы с триггерами

[Akina]

LEAD либо LAG.
Timescale поддерживает: https://docs.timescale.com/use-timescale/latest/co...

Типа

WITH cte AS (
SELECT timestamp t_from, LEAD(timestamp) OVER (ORDER BY timestamp) t_till
FROM sourcetable
)
SELECT *
FROM cte
WHERE t_till - t_from > '00:10:00'

[mayton2019]

Akina, это надо в ответы отписать.

[Akina]

mayton2019, Да лень мне рыть доки по timescale и выяснять, как там выглядят литералы времени и считаются длительности.

[mayton2019]

Я тоже не знаю timescale. Но мне кажется что LEAD/LAG - наиболее полный ответ.

Answer 1

[alexalexes]

-- Тестовая таблица, для тестов "на коленке"
with t as (select 1 id, TO_TIMESTAMP('2023.01.01 00:00:00', 'YYYY.MM.DD HH24:MI:SS') as timestamp union
select 2, TO_TIMESTAMP('2023.01.01 00:01:00', 'YYYY.MM.DD HH24:MI:SS') union
select 3, TO_TIMESTAMP('2023.01.01 00:02:00', 'YYYY.MM.DD HH24:MI:SS') union
select 4, TO_TIMESTAMP('2023.01.01 00:03:00', 'YYYY.MM.DD HH24:MI:SS') union
select 5, TO_TIMESTAMP('2023.01.01 00:05:00', 'YYYY.MM.DD HH24:MI:SS') union
select 6, TO_TIMESTAMP('2023.01.01 00:06:00', 'YYYY.MM.DD HH24:MI:SS') union
select 7, TO_TIMESTAMP('2023.01.01 00:07:00', 'YYYY.MM.DD HH24:MI:SS') union
select 8, TO_TIMESTAMP('2023.01.01 00:10:00', 'YYYY.MM.DD HH24:MI:SS') )
-- основной запрос
select t2.before_timestamp, t2.timestamp
  from (select t.timestamp, lag(t.timestamp) over (order by t.id) before_timestamp
          from t) t2
 where t2.before_timestamp is not null
   and extract(epoch from t2.timestamp - t2.before_timestamp) > 60 -- 60 сек. макс. допустимый интервал между событиями