To: pgsql-sql@postgresql.org
From: Seb <spluque@gmail.com>
Subject: Re: filtering based on table of start/end times
Date: Sun, 09 Nov 2014 12:43:22 -0600
Organization: Church of Emacs
Lines: 50
Message-ID: <87fvdskyet.fsf@net82.ceos.umanitoba.ca>
References: <8761eqbwip.fsf@net82.ceos.umanitoba.ca>
 <545DEFED.8030200@bandenkrieg.hacked.jp>
Mime-Version: 1.0
Content-Type: text/plain
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.4 (gnu/linux)
Cancel-Lock: sha1:2A/bv5aaR4b9Wc4qag8W6Ugl9g0=
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On Sat, 08 Nov 2014 11:26:53 +0100,
Tim Schumacher <tim@bandenkrieg.hacked.jp> wrote:

> I already sent this but used a wrong address. Sorry for the mess.
> On 07.11.2014 21:12, Seb wrote:
>> 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,


> Something like this should work:

> SELECT * FROM voltage_series AS vs LEFT JOIN voltage_log vl ON vs.time
> BETWEEN vl.time_beg AND vl.time_end WHERE vl.id IS NULL

> This is untested, but I think it should work.

Thank you all for your suggestions.  The above proved very fast with
the millions of records and several other joins involved.


-- 
Seb


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