Re: filtering based on table of start/end times

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,

Hello Seb,

Any reason this won't work for you?

SELECT *
  FROM voltage_series
  WHERE time NOT IN (SELECT DISTINCT time FROM voltage_series, voltage_log WHERE time BETWEEN time_beg AND time_end);

Might not be the fastest way to do it, if the tables are large.  Apologies if I've not understood your question properly.

Regards,
Bryan

-- 
______________
Postdoctoral Researcher
GA Cooperative Fish & Wildlife Research Unit
Warnell School of Forestry & Natural Resources
University of Georgia
Athens 30602-2152