Escolha o primeiro carimbo de data/hora antes de um intervalo, mas o último do dia se não houver um intervalo adequado

Esta é uma resposta completamente revisada que é muito mais eficiente que a anterior. A resposta antiga pode ser vista visualizando o histórico de edições ou como uma nota de rodapé na parte inferior desta postagem.

Um violino para todo o código abaixo pode ser encontrado no violino aqui .

Então, temos nossa tabela de teste:

CREATE TABLE test
(
  the_date TIMESTAMP NOT NULL
);

Preenchê-lo - registros adicionados por um dia sem intervalos > 3 horas:

INSERT INTO test VALUES


('2021-06-23 05:47:05'::TIMESTAMPTZ),     -- NO gaps > 3 hours on this date!
('2021-06-23 07:47:05'::TIMESTAMPTZ),
('2021-06-23 09:47:05'::TIMESTAMPTZ),
('2021-06-23 11:47:05'::TIMESTAMPTZ),
('2021-06-23 13:47:05'::TIMESTAMPTZ),
('2021-06-23 14:47:05'::TIMESTAMPTZ),  
('2021-06-23 16:47:05'::TIMESTAMPTZ),  
('2021-06-23 17:47:05'::TIMESTAMPTZ),
    

('2021-06-24 05:47:05'::TIMESTAMPTZ),  -- TWO gaps > 3 hours on this date

                                      -- 1st gap > 3 hours

('2021-06-24 09:47:05'::TIMESTAMPTZ),

                                      -- 2nd gap > 3 hours

('2021-06-24 13:47:05'::TIMESTAMPTZ),

('2021-06-24 14:47:05'::TIMESTAMPTZ),  -- added for testing
('2021-06-24 16:47:05'::TIMESTAMPTZ),  -- added for testing


('2021-06-24 17:47:05'::TIMESTAMPTZ);

E (demonstrando a lógica) executei o seguinte SQL:

SELECT
  the_date::DATE AS dat, 
  the_date AS td, 
  LEAD(the_date) 
    OVER (PARTITION BY the_date::DATE 
           ORDER BY the_date ASC) AS l_td,
  LEAD(the_date) 
    OVER (PARTITION BY the_date::DATE 
            ORDER BY the_date ASC) - the_date AS diff  -- for demonstration
FROM                                                   -- purposes - see diffs
  test                                                 -- > 3 HOUR - 2 on 24/06
ORDER BY dat, td;

Resultado:

       dat                      td                   l_td   diff
2021-06-23  2021-06-23 05:47:05+01  2021-06-23 07:47:05+01  02:00:00
2021-06-23  2021-06-23 07:47:05+01  2021-06-23 09:47:05+01  02:00:00
2021-06-23  2021-06-23 09:47:05+01  2021-06-23 11:47:05+01  02:00:00
2021-06-23  2021-06-23 11:47:05+01  2021-06-23 13:47:05+01  02:00:00
2021-06-23  2021-06-23 13:47:05+01  2021-06-23 14:47:05+01  01:00:00
2021-06-23  2021-06-23 14:47:05+01  2021-06-23 16:47:05+01  02:00:00
2021-06-23  2021-06-23 16:47:05+01  2021-06-23 17:47:05+01  01:00:00
2021-06-23  2021-06-23 17:47:05+01  NULL                    NULL        
2021-06-24  2021-06-24 05:47:05+01  2021-06-24 09:47:05+01  04:00:00
2021-06-24  2021-06-24 09:47:05+01  2021-06-24 13:47:05+01  04:00:00
2021-06-24  2021-06-24 13:47:05+01  2021-06-24 14:47:05+01  01:00:00
2021-06-24  2021-06-24 14:47:05+01  2021-06-24 16:47:05+01  02:00:00
2021-06-24  2021-06-24 16:47:05+01  2021-06-24 17:47:05+01  01:00:00
2021-06-24  2021-06-24 17:47:05+01  NULL                    NULL        
14 rows

Usamos a LEAD()função de janela. As funções de janela são extremamente poderosas e eu recomendo fortemente que você se esforce para aprender como usá-las - elas vão retribuir esse esforço muitas vezes!

• Ele fornece uma comparação entre o valor de the_datee o valor que o segue de acordo com os critérios no ORDER BY- você pode fazer muitas coisas inteligentes variando a ORDER BYcláusula na LEAD()própria função - isso e outros parâmetros variados podem ser vistos aqui .

• A PARTITION BY the_date::DATEcláusula é fornecer resultados separados para cada data que está em seu conjunto de dados. Observe em particular os NULLs - você não pode ter um LEAD que abrange dias graças ao particionamento, portanto, o valor de LEAD para o último registro de data e hora em qualquer dia sempre será NULL- isso está relacionado aos requisitos - veja abaixo.

Além disso, note que NULLmenos qualquer coisa é NULL(o mesmo para NULLmais...) - dizemos que NULLé "propagação".

Então, agora executamos este SQL:

WITH leads AS
(
    SELECT
      the_date::DATE AS dat, the_date AS td, LEAD(the_date)
          OVER (PARTITION BY the_date::DATE) AS l_td
    FROM
      test
)
SELECT DISTINCT ON(dat)
    dat AS "The date", td AS "Gap start or last ts"
FROM leads
WHERE l_td - td > INTERVAL '3 HOUR'
   OR l_td IS NULL
ORDER BY dat, td;

Resultado:

The date    Gap start or last ts
2021-06-23  2021-06-23 17:47:05+01
2021-06-24  2021-06-24 05:47:05+01

O resultado desejado! Mas, o que está acontecendo? A partir daqui :

O PostgreSQL tem uma construção realmente interessante e poderosa chamada SELECT DISTINCT ON. Não, este não é um DISTINTO típico. Isso é diferente. É perfeito quando você tem grupos de dados semelhantes e deseja extrair um único registro de cada grupo, com base em uma ordenação específica.

ou, dito de outra forma (do mesmo link):

Com DISTINCT ON, você diz ao PostgreSQL para retornar uma única linha para cada grupo distinto definido pela cláusula ON. Qual linha nesse grupo é retornada é especificada com a cláusula ORDER BY.

Ou da documentação do PostgreSQL aqui :

SELECT DISTINCT ON ( expressão [, ...] ) mantém apenas a primeira linha de cada conjunto de linhas em que as expressões fornecidas são avaliadas como iguais. As expressões DISTINCT ON são interpretadas usando as mesmas regras de ORDER BY (veja acima). Observe que a “primeira linha” de cada conjunto é imprevisível, a menos que ORDER BY seja usado para garantir que a linha desejada apareça primeiro. Por exemplo:

SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;

recupera o boletim meteorológico mais recente para cada local. Mas se não tivéssemos usado ORDER BY para forçar a ordem decrescente dos valores de tempo para cada local, teríamos obtido um relatório de um horário imprevisível para cada local.

A(s) expressão(ões) DISTINCT ON deve(m) corresponder à(s) expressão(ões) ORDER BY mais à esquerda. A cláusula ORDER BY normalmente conterá expressões adicionais que determinam a precedência desejada de linhas dentro de cada grupo DISTINCT ON.

Como você pode ver, isso (como as funções de janela) é obviamente uma ferramenta muito poderosa no arsenal do programador PostgreSQL e vale a pena dedicar tempo e esforço para aprender.

Uma abordagem alternativa interessante seria usar a ROW_NUMBER()função window, se você quiser as duas primeiras lacunas ou o último registro, da seguinte forma:

WITH leads AS
(
    SELECT
      the_date::DATE AS dat, the_date AS td,
      LEAD(the_date)
          OVER (PARTITION BY the_date::DATE) AS l_td
    FROM
      test
),
gaps AS
(
    SELECT
      dat, td,
      ROW_NUMBER()
          OVER (PARTITION BY dat ORDER BY td) AS rn
    FROM leads
    WHERE (l_td - td > INTERVAL '3 HOUR')
      OR (l_td IS NULL)
)
SELECT
    dat, td
FROM gaps
WHERE rn <= 2  -- NOTE 2!
ORDER BY dat, td;

Resultado:

       dat                      td
2021-06-23  2021-06-23 17:47:05+01
2021-06-24  2021-06-24 05:47:05+01
2021-06-24  2021-06-24 09:47:05+01

Observe que agora temos dois registros para 24/06/2021.

Finalmente, e apenas para registro, a solução original:

WITH long_gaps AS
(
  SELECT dat, MIN(td) AS gap
  FROM
  (
    SELECT
      the_date::DATE AS dat, the_date AS td, LEAD(the_date) OVER (PARTITIION BY the_date::DATE) AS l_td
    FROM
      test
  ) AS t1
  WHERE l_td - td > INTERVAL '3 HOUR'
  GROUP BY dat
),
short_gaps AS
(
  SELECT the_date::DATE AS dat2, MAX(the_date)
  FROM test
  WHERE the_date::DATE NOT IN (SELECT dat FROM long_gaps)
  GROUP BY dat2

)
SELECT dat AS "The date", gap AS "Gap start or last ts" FROM long_gaps
UNION 
SELECT * FROM short_gaps
ORDER BY 1;  -- parameter 1 which ORDERs BY the first field in the query

Resultado:

  The date     Gap start or last ts
2021-06-23   2021-06-23 17:47:05+01
2021-06-24   2021-06-24 05:47:05+01

Uma análise de desempenho das 3 soluções é fornecida na parte inferior do violino - mostra que a DISTINCT ONsolução é significativamente mais eficiente do que as outras - no entanto ROW_NUMBER(), tem potencial para ser mais flexível! No entanto, uma palavra de aviso - uma análise de desempenho em um conjunto de dados muito pequeno em um servidor sobre o qual não temos controle, nem qualquer ideia do que está acontecendo em outro lugar é potencialmente falha - eu aconselho que você faça um benchmark com conjuntos de dados razoáveis ​​em seu próprio hardware.

No futuro, quando você estiver fazendo perguntas dessa natureza, você poderia fornecer um violino com dados de amostra cobrindo todos os seus casos - ou seja, neste caso, onde há lacunas e onde não há. Isso reduz a possibilidade de erro e elimina a duplicação de esforços - ajude-nos a ajudá-lo. Além disso, sempre inclua sua versão do PostgreSQL.