Eu gostaria de armazenar intervalos de data/hora no PostgreSQL de alguns eventos arbitrários que têm um estado de aberto ou fechado e uma data/hora de quando o estado mudou.
Os eventos provenientes de uma API terão os seguintes dados para um único evento:
Request 1:
{
id: 1,
state: 'open',
date: '2020-02-17T10:00:00Z'
}
Request 2:
{
id: 1,
state: 'close',
date: '2020-02-17T10:10:00Z'
}
Request 3:
{
id: 1,
state: 'open',
date: '2020-02-17T11:00:00Z'
}
As solicitações podem vir em qualquer ordem, portanto, datas futuras podem vir antes de datas passadas ou estados nem sempre estão abertos -> fechar -> abrir -> fechar. Por exemplo, a API pode enviar os estados abertos para o mesmo evento um após o outro.
Eu estava pensando em usar o tstzrangepara salvar esses dados no banco de dados da seguinte forma:
CREATE TABLE events (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
event_id int,
validity tstzrange
);
Os estados abertos são capturados na coluna de validade e os estados fechados são as lacunas entre as colunas de validade. Por exemplo, se um único evento tiver os seguintes estados e datas/horas (usando apenas as horas para simplificar) nesta ordem:
state date/time
close 20:30
open 18:00
close 16:00
open 15:00
open 20:00
close 19:30
As linhas de validade devem se parecer com:
id event_id validity
1 1 [15:00, 16:00)
2 1 [18:00, 19:30)
3 1 [20:00, 20:30)
Event 1tem o estado openentre 15:00 - 16:00, estado closeentre 16:00 - 18:00, estado openentre 18:00 - 19:30e assim por diante.
Para ilustrar isso visualmente:
Meu problema é que os eventos não estão vindo em ordem, então não sei como manipular as colunas de validade individuais para inserir/atualizar essas linhas.