Re: filtering based on table of start/end times

<br /><div class="moz-cite-prefix">On 11/7/2014 3:12 PM, Seb wrote:<br /></div><blockquote
cite="mid:8761eqbwip.fsf@net82.ceos.umanitoba.ca"type="cite"><pre wrap="">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
withouttime 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
zoneNOT 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,

</pre></blockquote><br /> Hello Seb,<br /><br /> Any reason this won't work for you?<br /><br /><blockquote><tt>SELECT
*</tt><br/><tt>  FROM voltage_series</tt><br /><tt>  WHERE time NOT IN (SELECT DISTINCT time FROM voltage_series,
voltage_logWHERE time BETWEEN time_beg AND time_end);</tt><br /></blockquote><br /> Might not be the fastest way to do
it,if the tables are large.  Apologies if I've not understood your question properly.<br /><br /> Regards,<br />
Bryan<br/><br /><br /><br /><pre class="moz-signature" cols="72">-- 
 
______________
Postdoctoral Researcher
GA Cooperative Fish & Wildlife Research Unit
Warnell School of Forestry & Natural Resources
University of Georgia
Athens 30602-2152</pre>