Hello, I have a query and my query runs ok: SELECT
bin(time, INTERVAL ‘$interval’ MINUTE) AS time_interval,
MIN(measurement_value_double) AS lowerfence,
MAX(measurement_value_double) AS upperfence,
AVG(measurement_value_double) AS mean,
approx_percentile(measurement_value_double,0.5) AS median,
approx_percentile(measurement_value_double,0.25) AS q1,
approx_percentile(measurement_value_double,0.75) AS q3,
stddev(measurement_value_double) AS sd
FROM
“testTimestreamDB”.“testtimestreamTable”
WHERE
series_number = ‘6’
AND customer_name = ‘$CustomerName’
AND CAST(day(time) AS VARCHAR) = ‘$day’
AND CAST(month(time) AS VARCHAR) = ‘$month’
AND CAST(YEAR(time) AS VARCHAR) = ‘$year’
GROUP BY
bin(time, INTERVAL ‘$interval’ MINUTE)
and this is my ploty plot plugins’s script: let jumbo = ;
let allTimeIntervals = ;
data.series.forEach((s) => {
let seriesData = {};
s.fields.forEach((field) => {
seriesData[field.name] = field.values.buffer;
});
seriesData[‘type’] = “box”;
jumbo.push(seriesData);
// Extracting time intervals
const timeIntervalField = s.fields.find(field => field.name === ‘time_interval’);
if (timeIntervalField) {
const timeIntervals = timeIntervalField.values.buffer;
allTimeIntervals = allTimeIntervals.concat(Array.from(timeIntervals));
}
});
const layout = {
title: ‘Motor Temprature Plot for Each $interval-Minute Interval’,
xaxis: {
title: ‘Time Interval’,
type: ‘Category’,
showticklabels: true,
minor: {
tickvals: allTimeIntervals,
}
},
yaxis: {
autorange: true,
showgrid: true,
title: ‘Celsius’,
zeroline: true,
zerolinecolor: ‘rgb(255, 255, 255)’,
},
margin: {
l: 50,
r: 50,
b: 20,
t: 50
}
};
return {
data: jumbo,
layout: layout,
};
It creates box plots but in xlables doesn’t show the times and instead shows numbers (0,1,2…). I am using grafana 9.4. this is sample of my data raw(2024-05-06 11:10:00,
16.3,
16.9,
16.7,
16.7,
16.6,
16.8,
0.147).
I also tried another code that xlables are ok on it but it doesn’t show proper box plots and instead it shows only lines.// Initialize arrays to store box plot data
var x = ; // Time intervals
var min_values = ; // Minimum values
var max_values = ; // Maximum values
var median_values = ; // Median values
var Q1_values = ; // First quartile
var Q3_values = ; // Third quartile
var mean_values = ; // Third quartile
// Loop through the data series
for (var i = 0; i < data.series.length; i++) {
var series = data.series[i];
// Loop through the fields in the series
for (var j = 0; j < series.fields.length; j++) {
var field = series.fields[j];
// Check if the field name matches the desired value
if (field.name === ‘time_interval’) {
// Extract the time intervals
x = field.values.buffer;
} else if (field.name === ‘median’) {
// Extract the median values
median_values = field.values.buffer;
} else if (field.name === ‘q1’) {
// Extract Q1 values
Q1_values = field.values.buffer;
} else if (field.name === ‘q3’) {
// Extract Q3 values
Q3_values = field.values.buffer;
} else if (field.name===‘mean’){
//Extract Mean values
mean_values=field.values.buffer;
}else if (field.name===‘lowerfence’){
//Extract Mean values
min_values=field.values.buffer;
}else if (field.name===‘upperfence’){
//Extract Mean values
max_values=field.values.buffer;
}
}
}
// Generate box plot data
var traces = [{
x: x,
y: Q1_values,
type: ‘box’,
name: ‘Lower Quartile’,
boxmean: ‘sd’,
}, {
x: x,
y: median_values,
type: ‘box’,
name: ‘Median’,
boxmean: ‘sd’,
}, {
x: x,
y: Q3_values,
type: ‘box’,
name: ‘Upper Quartile’,
boxmean: ‘sd’,
},{
x: x,
y: mean_values,
type: ‘box’,
name: ‘Mean’,
boxmean: ‘sd’,
},{
x: x,
y: max_values,
type: ‘box’,
name: ‘max’,
boxmean: ‘sd’,
},{
x: x,
y: min_values,
type: ‘box’,
name: ‘Min’,
boxmean: ‘sd’,
}];
// Return the data and layout for the Plotly Panel
return {
data: traces,
config: {
displayModeBar: true,
},
layout: {
title: ‘Velocity Box Plot for Each 1-Minute Interval’,
xaxis: {
title: ‘Time Interval’
},
yaxis: {
autorange: true,
showgrid: true,
title: ‘Measurement Value’,
zeroline: true,
zerolinecolor: ‘rgb(255, 255, 255)’,
},
margin: {
l: 50,
r: 50,
b: 20,
t: 10
},
showlegend: true,
}
};
