filtering based on table of start/end times

From: Seb <spluque(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: filtering based on table of start/end times
Date: 2014-11-07 20:12:14
Message-ID: 8761eqbwip.fsf@net82.ceos.umanitoba.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi,

At first glance, this seemed simple to implement, but this is giving me
a bit of a headache.

Say we have a table as follows:

CREATE TABLE voltage_series
(
voltage_record_id integer NOT NULL DEFAULT nextval('voltage_series_logger_record_id_seq'::regclass),
"time" timestamp without time zone NOT NULL,
voltage numeric,
CONSTRAINT voltage_series_pkey PRIMARY KEY (voltage_record_id));

So it contains a time series of voltage measurements. Now suppose we
have another table of start/end times that we'd like to use to filter
out (or keep) records in voltage_series:

CREATE TABLE voltage_log
(
record_id integer NOT NULL DEFAULT nextval('voltage_log_record_id_seq'::regclass),
time_beg timestamp without time zone NOT NULL,
time_end timestamp without time zone NOT NULL,
CONSTRAINT voltage_log_pkey PRIMARY KEY (record_id));

where each record represents start/end times where the voltage
measurement should be removed/kept. The goal is to retrieve the records
in voltage_series that are not included in any of the periods defined by
the start/end times in voltage_log.

I've looked at the OVERLAPS operator, but it's not evident to me whether
that is the best approach. Any tips would be appreciated.

Cheers,

--
Seb

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tim 2014-11-07 21:14:31 Re: filtering based on table of start/end times
Previous Message hubert depesz lubaczewski 2014-11-05 14:06:18 Re: Bug or feature in AFTER INSERT trigger?