INTERPOLATE PREVIOUS VALUE

reli · February 2021

Hi all,
I use INTERPOLATE PREVIOUS VALUE by time in query and I didn't understand the results,

CREATE TABLE data.timevalue
(
    unit_id int,
    field_id int,
    time_ timestamptz,
    value_ numeric(37,15),
  )

the data in the table are in different times for the different fields in the same unit.
I run 2 queries :
*Query 1

SELECT value_ ,time_ 
            FROM data.timevalue  
            WHERE time_  >='2019-09-21 07:43:00.000000' AND unit_id = 17
            AND time_ <='2019-09-21 14:59:00.000000'
            AND field_id = 24;

*Query 2

SELECT time_ ,
       field_Temp.value_ AS Temp
      FROM data.timevalue 
FULL OUTER JOIN ( SELECT value_ ,time_ 
            FROM data.timevalue
            WHERE time_  >= '2019-09-21 07:43:00.000000' 
            and unit_id = 17
            AND time_ <='2019-09-21 14:59:00.000000'
            AND field_id = 24
             )--Temp 
AS field_Temp ON time_ INTERPOLATE PREVIOUS VALUE field_Temp.time_
WHERE time_  >= '2019-09-21 07:43:00.000000' AND time_ <='2019-09-21 14:59:00.000000' AND unit_id = 17;

the results:

And now the questions:
1.In the png in the results, where did the data from the yellow part come from?
2.Is it correct to execute query # 2 when I have a lot of field_id like field =24 with different time, sometimes about the same time and sometimes a difference of a few single seconds and I need one report to all of them?
Thank you very much for your help!!

marcothesane · February 2021

You must have one or more entries in the timevalue table whose unit_id is <> 17 or whose field_id is <> 24 and whose value_ is 25.6.

marcothesane · February 2021

and whose time_ is between '2019-09-21 08:53:00.0' and '2019-09-21 08:53:45.8'

marcothesane · February 2021

Can you paste (not as image, but as text) the rows from timevalue with time_ between '2019-09-21 07:43:00' and '2019-09-21 14:59:00', with unit_id 17 and field_id 24 and with a few other unit_ids and perhaps a few other field_ids, so we can illustrate the behaviour on a data cutout?

reli · February 2021

@marcothesane said:
and whose time_ is between '2019-09-21 08:53:00.0' and '2019-09-21 08:53:45.8'

I found the raw with the value 25.6!! Tank a lot @marcothesane
now for the next qution 2:

SELECT time_ ,
       field_Temp.value_ AS Temp,
       field_ph.value_ as Ph
      FROM data.timevalue 
FULL OUTER JOIN ( SELECT value_ ,time_ 
            FROM data.timevalue
            WHERE time_  >= '2019-09-21 07:43:00.000000' 
            and unit_id = 17
            AND time_ <='2019-09-21 14:59:00.000000'
            AND field_id = 24
             )--Temp 
AS field_Temp ON time_ INTERPOLATE PREVIOUS VALUE field_Temp.time_
FULL OUTER JOIN ( SELECT value_ ,time_ 
            FROM data.timevalue
            WHERE time_  >= '2019-09-21 07:43:00.000000' 
            and unit_id = 17
            AND time_ <='2019-09-21 14:59:00.000000'
            AND field_id = 23
             )--ph 
AS field_ph ON time_ INTERPOLATE PREVIOUS VALUE field_ph.time_
WHERE time_  >= '2019-09-21 07:43:00.000000' AND time_ <='2019-09-21 14:59:00.000000' AND unit_id = 17;

this is the bast way to get all full report for all the fields in one unit_id? in one unit there is 20 different fields in the example I select only tow fields .
I add attach file ,example of the data.

reli · February 2021

Can anyone look at the query and tell me if it's a reasonable solution to a problem I have?

INTERPOLATE PREVIOUS VALUE

Answers

Leave a Comment