To: pgsql-sql@postgresql.org
From: Seb <spluque@gmail.com>
Subject: filtering based on table of start/end times
Date: Fri, 07 Nov 2014 14:12:14 -0600
Organization: Church of Emacs
Lines: 37
Message-ID: <8761eqbwip.fsf@net82.ceos.umanitoba.ca>
Mime-Version: 1.0
Content-Type: text/plain
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.4 (gnu/linux)
Cancel-Lock: sha1:E0akSLpyOJV7tTBBE2YqmVcHW2M=
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql