agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedfiltering based on table of start/end times
5+ messages / 4 participants
[nested] [flat]
* filtering based on table of start/end times
@ 2014-11-07 20:12 Seb <spluque@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Seb @ 2014-11-07 20:12 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: filtering based on table of start/end times
@ 2014-11-07 21:14 Tim <tim@datenknoten.me>
parent: Seb <spluque@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Tim @ 2014-11-07 21:14 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: filtering based on table of start/end times
@ 2014-11-07 21:58 Bryan L Nuse <nuse@uga.edu>
parent: Seb <spluque@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Bryan L Nuse @ 2014-11-07 21:58 UTC (permalink / raw)
To: Seb <spluque@gmail.com>; 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: filtering based on table of start/end times
@ 2014-11-08 10:26 Tim Schumacher <tim@bandenkrieg.hacked.jp>
parent: Seb <spluque@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Tim Schumacher @ 2014-11-08 10:26 UTC (permalink / raw)
To: pgsql-sql
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.
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: filtering based on table of start/end times
@ 2014-11-09 18:43 Seb <spluque@gmail.com>
parent: Tim Schumacher <tim@bandenkrieg.hacked.jp>
0 siblings, 0 replies; 5+ messages in thread
From: Seb @ 2014-11-09 18:43 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2014-11-09 18:43 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-11-07 20:12 filtering based on table of start/end times Seb <spluque@gmail.com>
2014-11-07 21:14 ` Tim <tim@datenknoten.me>
2014-11-07 21:58 ` Bryan L Nuse <nuse@uga.edu>
2014-11-08 10:26 ` Tim Schumacher <tim@bandenkrieg.hacked.jp>
2014-11-09 18:43 ` Seb <spluque@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox