Sum time series with slightly different timestamps

Grafana Dashboards
1

Hi Grafana Community!

I am running Grafana in docker (latest) on Ubuntu 16.04 on a ‘shuttle’ mini-computer.
I am using the infinity data-source plugin to get energy data from a REST API (json).

I want to graph electric power (input or output). For that I need to sum value from three phases (IP1, IP2 and IP3).
I get time series from the API {timestamp (s) : value} for each phase.

My problem is that the timestamps are slightly different for each series, with about 1 second difference. I don’t care about time precision.

image
image1941×355 21.7 KB

I want to be able to join/merge my series but I have not been able to, because of that time difference.
the difference varies from one request to the next so I can’t just add a few seconds to cheat.

Is there a way to ‘round’ my timestamps so that I can merge my series?

I tried creating a new column as timestamp/10 but I didn’t find a floor function and grafana keeps the decimal value. I still coudn’t merge my series.

I also tried using the group by transformation on the time column but couldn’t make it work like this either.

i am open to any suggestion.

Thanks a lot!
Have a nice day!

2

Please provide sample json data.

1 Like
3

{
“success”: true,
“records”: {
“data”: {
“17”: [
[
1517018,
null
],
[
1648132784,
2812
],
[
1649216369,
2481
],
[
1650299954,
2819
],
[
1651383539,
3143
],
[
1652467124,
2848
],
[
1653550709,
2836
],
[
1654634294,
3589
],
[
1655717879,
3334
],
[
1656801464,
2821
],
[
1657885049,
2879
],
[
1658968634,
2151
],
[
1660052219,
485
],
[
1661135804,
2202
],
[
1662219389,
3911
],
[
1663302974,
3106
],
[
1664386559,
908
]
],
“18”: ,
“19”:
},
“meta”: {
“17”: {
“code”: “IP1”,
“description”: “Input power 1”,
“formatValueOnly”: “%.0F”,
“formatWithUnit”: “%.0F W”,
“axisGroup”: null
},
“18”: {
“code”: “IP2”,
“description”: “Input power 2”,
“formatValueOnly”: “%.0F”,
“formatWithUnit”: “%.0F W”,
“axisGroup”: null
},
“19”: {
“code”: “IP3”,
“description”: “Input power 3”,
“formatValueOnly”: “%.0F”,
“formatWithUnit”: “%.0F W”,
“axisGroup”: null
}
},
“instance”: 0
}
}

1 Like
4

I’m sorry, I found a way.
And the JSON I provided didn’t fit my problem.

Thanks a lot anyway yosiasz!
Have a nice day.

1 Like
5

Would be nice if you posted the solution so others can benefit

1 Like
6

I had a mistake in my query, le start date of queried data wasn’t properly set.
With the start date properly set, the timestamp aligned and I could just use ‘merge’ and a few other transformations to get the result I wanted.

here is the result :

image
image2083×1052 142 KB

Thanks a million!

1 Like