Merging two different Measurement with similar but NonIdentical timeStamp?

Fluxlang
1

Hello Everyone,

We are currently trying form a query to get the runningHours data from InfluxDb.
I have tried my best to add a sample of a data. There are two differenet measurement namely measurement_one and measurement_two.
We would like to get the data of “runningHours” with respect to time-interval(i.e. e.g. get the total running hours now()-6h separately for when the dustbinFull was true and false. In the picture. I would like to plot the values as pie chart to show the percent of runningHours in both the cases.

Thank you in advance

_time Measurement _field _value
2023-06-22 02:00:00 GMT+2 measurement_one dustbinFull TRUE
2023-06-22 02:00:00 GMT+3 measurement_one dustbinFull TRUE
2023-06-22 02:00:00 GMT+4 measurement_one dustbinFull FALSE
2023-06-22 03:00:00 GMT+2 measurement_one dustbinFull FALSE
2023-06-22 03:00:00 GMT+3 measurement_one dustbinFull FALSE
2023-06-22 03:00:00 GMT+4 measurement_one dustbinFull TRUE
2023-06-22 04:00:00 GMT+2 measurement_one dustbinFull TRUE
2023-06-22 04:00:00 GMT+3 measurement_one dustbinFull TRUE
2023-06-22 04:00:00 GMT+4 measurement_one dustbinFull FALSE
2023-06-22 05:00:00 GMT+2 measurement_one dustbinFull FALSE
_time Measurement _field _value
2023-06-22 02:00:00 GMT+2 measurement_two runningHours 10
2023-06-22 02:00:00 GMT+3 measurement_two runningHours 11
2023-06-22 02:00:00 GMT+4 measurement_two runningHours 12
2023-06-22 03:00:00 GMT+2 measurement_two runningHours 13
2023-06-22 03:00:00 GMT+3 measurement_two runningHours 14
2023-06-22 03:00:00 GMT+4 measurement_two runningHours 15
2023-06-22 04:00:00 GMT+2 measurement_two runningHours 16
2023-06-22 04:00:00 GMT+3 measurement_two runningHours 17
2023-06-22 04:00:00 GMT+4 measurement_two runningHours 18
2023-06-22 05:00:00 GMT+2 measurement_two runningHours 21

PS: the date show is just for an example and the timestamp values in real scenario will be always vary and they won’t be identical in both the measurement

2

@Anaisdg or @scott or @grant1 Can you please take a look and help me ??? Thanks in advance

3

Hi @Belly_Musketier

Is there any way for you to ingest both field (dustbinFull and runningHours) into the same single measurement with the same timestamp? Then it would be a straightforward pivot and some sort of state duration function.

If not, then I think you need to normalize them first, then join them into a single table via a union function, then do the pivot to calculate what you want.

4

Hello @grant1 Thank you for your response yes!! Unfortunately we cannot do the option one i.e. both in one measurement but I will give a try to option second. While going through the multiple threads on the forum I also found this Merge data from 2 db not sure if this will help us or not. I will you updated