Message-ID: <545D3637.1040902@datenknoten.me>
Date: Fri, 07 Nov 2014 22:14:31 +0100
From: Tim <tim@datenknoten.me>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.1.2
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: filtering based on table of start/end times
References: <8761eqbwip.fsf@net82.ceos.umanitoba.ca>
In-Reply-To: <8761eqbwip.fsf@net82.ceos.umanitoba.ca>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org


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.

greetings

Tim


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