From: | Seb <spluque(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: filtering based on table of start/end times |
Date: | 2014-11-09 18:43:22 |
Message-ID: | 87fvdskyet.fsf@net82.ceos.umanitoba.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Sat, 08 Nov 2014 11:26:53 +0100,
Tim Schumacher <tim(at)bandenkrieg(dot)hacked(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Neyman | 2014-11-10 15:33:49 | Re: Using aggregates to get sums and partial sums in one query |
Previous Message | Tim Schumacher | 2014-11-08 10:26:53 | Re: filtering based on table of start/end times |