Hi everyone,
System:
I am using Grafana cloud v9.1.5 and my data source is Google BigQuery.
The data is the time stream data and the time internal of each data is 1sec, 10sec and 60sec.
Question:
I would like to control the total of data points depends on time range.
For example, if every second has 1 data point and then there are 604,800 data points in total in last 7 days.
When the time range is last 24 hours, I would like to display total data points which only less than or equeal to 12k which is maximum 500 data points a hour. In this case, the time interval between data points should be 7.2 seconds.
However, the original data points are only scale to seconds not milliseconds, the time interval should be 8 seconds.
At the end, the panel should display only 10800 data points.
(24 hours * 60 minutes * 60 seconds) / (8 seconds) = 10800 data points.
The displaied data points can’t be the averange value or the max/min value, the original value are expected.
The displayed data points can’t be the average value or the max/min value, the original value are expected. It means “09/29 10:00” and “09/29 10:08” can be displayed, and skip "09/29 10:01 " ~ “09/29 10:07”.
The data points should changed if the different time range is selected and the total of data points of different time tange should be modified easily.
At this time:
I had tried with Query options of BigQuery and the SQL code like
SELECT
Date AS time
, Id AS metric
, Value
FROM (
SELECT
Date
, Id
, Value
, TIMESTAMP_DIFF(TIMESTAMP(Date), TIMESTAMP(MIN(Date) OVER(ORDER BY Date)), SECOND) AS step
)
WHERE MOD(step, ${time_step}) = 0
In above SQL code, I can only ensure the interval between each data points depends on the selected time range. But this way might cause the missing data points sometimes, especially the data with 10 second time interval in original.
Really appreciate if any information and reference
Best regards,