| 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: | Whole Thread | Raw Message | 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
| 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? |