agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Seb <spluque@gmail.com>
To: pgsql-sql@postgresql.org
Subject: matching against start/end times and diagnostic values (was: filtering based on table of start/end times)
Date: Wed, 12 Nov 2014 15:49:57 -0600
Message-ID: <87fvdonl6i.fsf_-_@net82.ceos.umanitoba.ca> (raw)
References: <8761eqbwip.fsf@net82.ceos.umanitoba.ca>
	<545DEFED.8030200@bandenkrieg.hacked.jp>
	<87fvdskyet.fsf@net82.ceos.umanitoba.ca>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

On Sun, 09 Nov 2014 12:43:22 -0600,
Seb <spluque@gmail.com> wrote:

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

[...]

>> 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.

Sorry to come back with a related issue, which is proving troublesome.
There's another log table, that looks just like voltage_log, but has an
additional column with an integer indicating what problem occurred
during the period:

CREATE TABLE voltage_diagnostic_log
(
  record_id serial,
  time_beg timestamp without time zone NOT NULL,
  time_end timestamp without time zone NOT NULL,
  diagnostic integer,
  CONSTRAINT voltage_log_pkey PRIMARY KEY (record_id));

So that a view can be built for the voltage_series table where its
columns can be adjusted based on the diagnostic integer (there are other
columns besides voltage in the actual table), if the time stamp falls
within a period of the voltage_diagnostic_log table.  To illustrate, the
view needs to be able to have field definitions such as (pseudo-code):

CASE WHEN diagnostic=1 THEN voltage * 0.88
ELSE voltage
END AS voltage_corrected,
CASE WHEN diagnostic=2 THEN pressure - 2.5
ELSE pressure
END AS pressure_corrected,

The problem is that each record in voltage_series can have several
matching records in voltage_diagnostic_log.  Any insights welcome!


-- 
Seb



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

view thread (2+ messages)  latest in thread

Message-ID: <87fvdonl6i.fsf_-_@net82.ceos.umanitoba.ca>
Permalink:  ../87fvdonl6i.fsf_-_@net82.ceos.umanitoba.ca/
Also on:    postgresql.org/message-id/87fvdonl6i.fsf_-_@net82.ceos.umanitoba.ca

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: spluque@gmail.com
  Subject: Re: matching against start/end times and diagnostic values (was: filtering based on table of start/end times)
  In-Reply-To: <87fvdonl6i.fsf_-_@net82.ceos.umanitoba.ca>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox