time series for multiple metrics with nullable times

[waltervl]

See https://grafana.com/docs/grafana/latest/datasources/mssql/#time-series-queries

Does SQLite datasource support this to have a column metric and value have the metric column split the values over multiple lines in graph ? It does not seem to do so. I only get multiple value lines in a graph if I create multiple value columns by SQL query. Unfortunately SQLite does not have a PIVOT function so that is hard to do for my database.

Workaround of course is to create multiple Queries on a graph panel. So just wondering if this plugin supports time series like MS SQL with time and one metric column?

[fr-ser]

Could you maybe give me an example query (for MS SQL) and what you would expect to see? Right now it is still a bit hard to grasp what set of features is required exactly.

[waltervl]

From the documentation:

CREATE TABLE [event] ( time_sec bigint, description nvarchar(100), tags nvarchar(100), )

CREATE TABLE metric_values ( time datetime, measurement nvarchar(100), valueOne int, valueTwo int, )

INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES('2018-03-15 12:30:00', 'Metric A', 62, 6) INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES('2018-03-15 12:30:00', 'Metric B', 49, 11) ... INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES('2018-03-15 13:55:00', 'Metric A', 14, 25) INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES('2018-03-15 13:55:00', 'Metric B', 48, 10)

Example with one value and one metric column.

SELECT time, valueOne, measurement as metric FROM metric_values WHERE $__timeFilter(time) ORDER BY 1

When the above query is used in a graph panel, it will produce two series named Metric A and Metric B with the values valueOne and valueTwo plotted over time.

[fr-ser]

The plugin supports time-series formatting (you need to select time series as format). You can try with this query

with data(time, temperature, city) AS (VALUES
(724125600, 10, 'London'), (724161600, 11, 'London'),
(724161600, 20, 'New York'), (724197600, 21, 'New York'),
(724125600, 25, 'Washington'), (724197600, 26, 'Washington')
) SELECT * from data
order by time

[fr-ser]

The query you provided translates into something like the below SQL. This cannot be formatted (yet) as a time series, as some of the metrics have null values. This is indeed a new feature, which I might take a look at

WITH metric_values(time, metric, valueOne, valueTwo) AS ( VALUES
    ('2021-01-28 12:30:00', 'Metric A', 62, 6),
    ('2021-01-28 12:30:00', 'Metric B', 49, 11),
    ('2021-01-28 13:55:00', 'Metric A', 14, 25),
    ('2021-01-28 13:55:00', 'Metric B', 48, 10)
)
SELECT time, valueOne, metric
FROM metric_values
ORDER BY 1

[waltervl]

The query you provided translates into something like the below SQL. This cannot be formatted (yet) as a time series, as some of the metrics have null values. This is indeed a new feature, which I might take a look at

WITH metric_values(time, metric, valueOne, valueTwo) AS ( VALUES
    ('2021-01-28 12:30:00', 'Metric A', 62, 6),
    ('2021-01-28 12:30:00', 'Metric B', 49, 11),
    ('2021-01-28 13:55:00', 'Metric A', 14, 25),
    ('2021-01-28 13:55:00', 'Metric B', 48, 10)
)
SELECT time, valueOne, metric
FROM metric_values
ORDER BY 1

I do not know where you see null values? I see them because of the incorrect time format If I use

WITH metric_values(time, metric, valueOne, valueTwo) AS ( VALUES
('2021-01-28T12:30:00Z', 'Metric A', 62, 6),
('2021-01-28T12:30:00Z', 'Metric B', 49, 11),
('2021-01-28T13:55:00Z', 'Metric A', 14, 25),
('2021-01-28T13:55:00Z', 'Metric B', 48, 10)
)
SELECT time, valueOne, metric
FROM metric_values
ORDER BY 1

Then I see a time series with 2 lines. Conclusion: It supports it!. Nice. Now I have to find out why it is not working with my database...... Thank you for the support!!

[waltervl]

I found the issue: The metric in my case is an integer (BIGINT(10)). I converted it in the query into a string by adding a prefix (Temp_). When metric is an integer it seems not to be recognized as a metric.

Just for the record (for Domoticz Users) showing the temperature log of 3 of my temperature devices (11, 97, 213):

WITH converted AS (
   SELECT Temperature ,  REPLACE(Date, ' ','T') || 'Z' AS time, 'Temp_' || DeviceRowID AS metric FROM Temperature  Where  DeviceRowID in (11, 97, 213)
)
SELECT time, metric, Temperature FROM converted ORDER BY time

[fr-ser]

Ah ok, I was confused with the error message I got. I assumed you can only plot the graph if all metrics have the same timestamps, but that was just the formating of the timestamp to NULL.

In that case, I would consider the problem solved and close the issue :ok_hand: