tabela (simplificada)
Table "public.events"
Column | Type | Modifiers
------------------+-----------------------------+--------------------------------------------------------
id | integer | not null default nextval('events_id_seq'::regclass)
duration | integer | not null
start_at | timestamp without time zone |
Indexes:
"events_pkey" PRIMARY KEY, btree (id)
"my_idx" gist (tsrange(start_at, end_at(events.*), '[)'::text))
função
CREATE FUNCTION end_at(rec events)
RETURNS timestamp without time zone
IMMUTABLE
LANGUAGE SQL
AS $$
SELECT $1.start_at + ($1.duration * ('00:00:01'::interval));
$$;
o que já estou fazendo com sucesso
O índice é usado para consultas como esta:
-- check if current time is within the start and end times
-- of event
where localtimestamp <@ tsrange(start_at, events.end_at, '[)')
E funciona bem.
O que eu quero fazer
Quero consultar eventos em que a hora atual é depois que eles terminaram. Maneiras que eu conheço de como fazer isso:
where tsrange(localtimestamp, localtimestamp, '[]') >> tsrange(start_at, events.end_at, '[)')
. Tenho certeza de que essa é a semântica que quero eexplain analyze
diz que está usando o índice, mas é um pouco feio e estou imaginando se há uma maneira melhor de expressar isso (e também estou vagamente incerto é a semântica que quero, pois Eu sou novo em intervalos).where localtimestamp > upper(tsrange(start_at, events.end_at, '[)'))
+ um índice btree emupper(tsrange(start_at, events.end_at, '[)'))
. Isso funcionará bem, mas requer manter outro índice por perto.where localtimestamp > events.end_at
. + um índice btree emevents.end_at
. Mesma situação acima.
Existe uma maneira mais elegante (ou correta) de alcançar o primeiro ponto acima?
Alguma outra ideia de como fazer isso?
O Postgres não suporta nenhum operador para isso. Como apontado por @evan-carroll, provavelmente poderia e deveria.
Então, a melhor solução é
Você pode ver os operadores GiST aqui . Você pode ver a lista em
range_ops
,Como você indicou, o que você provavelmente quer é
<<
,>>
. eu usaria<<
.É feio? Sim. O tipo de range é ótimo se você estiver trabalhando com ranges em ambos os lados, aqui você não está. Você nem está armazenando o tipo de intervalo na mesa. Eu recomendaria que você fizesse isso.
Não tenho certeza se essa condição de índice funciona,
Você testou isso? Quero dizer, o PostgreSQL pode verificar o
start_at
funcionamento, minha suposição é que não é inteligente o suficiente para trabalhar comend_at
(events.*
e se um dos valores (colunas) usados noend_at
arquivo for alterado da criação da tabela para a chamada? Mesmo que nunca aconteça, como o PostgreSQL saiba disso). Eu armazenaria otsrange
na tabela e descartaria todas as colunas associadas.Ignore o abaixo, olhando para ele.
Além disso, você pode considerar torná-lo mais curto não construindo o intervalo rhs.Ou algo parecido, se você preferir esse formato. Ou o seguinte, que parece um antipadrão.
Ou você pode usar
tstzrange
(o que provavelmente é uma ideia melhor),