From: | Seb <spluque(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | matching against start/end times and diagnostic values (was: filtering based on table of start/end times) |
Date: | 2014-11-12 21:49:57 |
Message-ID: | 87fvdonl6i.fsf_-_@net82.ceos.umanitoba.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Sun, 09 Nov 2014 12:43:22 -0600,
Seb <spluque(at)gmail(dot)com> wrote:
> On Sat, 08 Nov 2014 11:26:53 +0100,
> Tim Schumacher <tim(at)bandenkrieg(dot)hacked(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Seb | 2014-11-14 17:04:52 | Re: matching against start/end times and diagnostic values |
Previous Message | Andreas Joseph Krogh | 2014-11-10 18:52:42 | Re: Using aggregates to get sums and partial sums in one query |