agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Seb <spluque@gmail.com>
To: pgsql-sql@postgresql.org
Subject: filtering based on table of start/end times
Date: Fri, 07 Nov 2014 14:12:14 -0600
Message-ID: <8761eqbwip.fsf@net82.ceos.umanitoba.ca> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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



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

view thread (5+ messages)  latest in thread

Message-ID: <8761eqbwip.fsf@net82.ceos.umanitoba.ca>
Permalink:  ../8761eqbwip.fsf@net82.ceos.umanitoba.ca/
Also on:    postgresql.org/message-id/8761eqbwip.fsf@net82.ceos.umanitoba.ca

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: spluque@gmail.com
  Subject: Re: filtering based on table of start/end times
  In-Reply-To: <8761eqbwip.fsf@net82.ceos.umanitoba.ca>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox