From: | Bryan L Nuse <nuse(at)uga(dot)edu> |
---|---|
To: | Seb <spluque(at)gmail(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: filtering based on table of start/end times |
Date: | 2014-11-07 21:58:45 |
Message-ID: | 545D4095.8040106@uga.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 11/7/2014 3:12 PM, 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,
>
Hello Seb,
Any reason this won't work for you?
SELECT *
FROM voltage_series
WHERE time NOT IN (SELECT DISTINCT time FROM voltage_series,
voltage_log WHERE time BETWEEN time_beg AND time_end);
Might not be the fastest way to do it, if the tables are large.
Apologies if I've not understood your question properly.
Regards,
Bryan
--
______________
Postdoctoral Researcher
GA Cooperative Fish & Wildlife Research Unit
Warnell School of Forestry & Natural Resources
University of Georgia
Athens 30602-2152
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Joseph Krogh | 2014-11-08 01:18:55 | Using aggregates to get sums and partial sums in one query |
Previous Message | Tim | 2014-11-07 21:14:31 | Re: filtering based on table of start/end times |