Hi Community! Here I go again @mikhailvolkov and @yosiasz with Echarts plugin 
Frecuently I work with time series storaged in a relational database like postgresql. I would like to start using line charts from Echarts plugin in order to build a chart similar to this in the image. I hope i could build it more beatiful
As you can see there are 5 time series (each of them in a different color). It seems so easy isn’t?.
The first challenge is to map the data from SQL query into echarts:
- MY SQL QUERY
SELECT
DATETIME AS TIME,
REGION,
SUM(A)/SUM(B) AS INDEX
FROM schema.table t
GROUP BY 1,2
GROUP BY 2,1
The data look like this in the image below. The “region” field has 5 unique values. In the “time” field I have a time range expresed in date format (a row per day) and in “index” field there are numerical values. So, a line in the chart is defined as:
- A unique value in “region” field
- A time range taken from “time” field (it can be several days).
- A list of numerical values taken from “index” field.
To map the dimentions I decided to use “Dataset” (The echarts component). Here starts my headeaches 
I used the following code:
let time = [],
region = [],
index = [];
const datablock = data.series.map((s) => {
const time = s.fields.find((f) => f.name === 'time').values.buffer;
const region = s.fields.find((f) => f.name === 'region').values.buffer;
const index = s.fields.find((f) => f.name === 'index').values.buffer;
return time.map((d, i) => [d, region[i], index[i]]);
})[0];
return {
dataset: {
dimensions: ['time', 'region', 'index'],
source: datablock
},
title: {
left: 'center',
text: 'is it my best chart?'
},
xAxis: {
type: 'time',
},
yAxis: {
type: 'value'
},
series: [
{
type: 'line'
encode: { x: 'time', y: 'index' }
}
]
};
Here it is the chart: The sql query is the same in both charts. Unfortunatelly it looks worse!
So, I would like to ask some questions:
- Is the correct way to use Dataset in this kind of Sql query for time series? I think there should be a better way to do it.
- Is there a way to assign a color for each line according to the “region” field?
I read the documentation both apache echarts and grafana plugin but i could not improve this chart. All the examples in the documentation are built taking static data.
It would be great If you could help me with some tips
Thanks in adavance.
BR
Jorge