Outer Join two data by time, but results are not ordered by time

[Xueying]

Hi, I think the Outer Join works improperly when there is missing values in data.

I have two queries, one select “value” from table “status”, and the other select “FiltratePressure” from table “wide_data”. Both queries “Group by the data” with15s interval.

When I do outer join by time using Transform, I noticed that the combined table was not completely in time order. See pic below.

“2021-10-24 03:53:15” at the bottom is the outlier. The FiltratePressure at this time stamp is missing, and Outer Join doesn’t put this line in the correct order. This causes issues when I applied preprocessing → imputing_missing_value → fill → forward_fill as a following step.

Similar issues find here.https://community.grafana.com/t/time-series-not-sorted-by-time-after-outer-join-by-time/33794 No solution posted yet.

[Xueying]

Not a solution, just test it out with manual query.

SELECT
$__timeGroupAlias(A.t_stamp,15s),
avg(if(A.value>100,1,0)) AS “OperationState”,
avg(B.FiltratePressure) AS “FiltratePressure”
FROM wide_data AS B
LEFT JOIN status AS A
ON year(A.t_stamp)=year(B.t_stamp)
and month(A.t_stamp)=month(B.t_stamp)
and day(A.t_stamp) = day(B.t_stamp)
and hour(A.t_stamp) = hour(B.t_stamp)
and minute(B.t_stamp) = minute(A.t_stamp)
WHERE
$__timeFilter(A.t_stamp) AND
A.name = ‘OperationState’
GROUP BY 1
ORDER BY $__timeGroup(A.t_stamp,15s)

It is ordered by time, but a lot of data points are omitted.