Stuck trying to make a time series with Grafana and SQLite

[dwhite21]

Hello - I am brand new to Grafana, trying to following the “Getting Started” and “Introduction” documentation, but I’m hopelessly lost trying to map time series dimensions (which is what I think I want, though the term is new to me) I have Grafana 10 installed locally along with the SQLite data source plugin, and a sample DB that looks like this.

CREATE TABLE temperatures (
  site VARCHAR,
  time VARCHAR,
  temp NUMBER
);

INSERT INTO temperatures VALUES ('New York',    '2023-11-02T00:00:00Z', '33.4');
INSERT INTO temperatures VALUES ('New York',    '2023-11-02T00:02:00Z', '32.3');
INSERT INTO temperatures VALUES ('New York',    '2023-11-02T00:04:00Z', '31.2');
INSERT INTO temperatures VALUES ('Los Angeles', '2023-11-02T00:05:00Z', '65.7');
INSERT INTO temperatures VALUES ('New York',    '2023-11-02T00:06:00Z', '32.3');
INSERT INTO temperatures VALUES ('Los Angeles', '2023-11-02T00:07:00Z', '66.2');
INSERT INTO temperatures VALUES ('New York',    '2023-11-02T00:08:00Z', '34.2');
INSERT INTO temperatures VALUES ('Los Angeles', '2023-11-02T00:09:00Z', '66.3');
INSERT INTO temperatures VALUES ('New York',    '2023-11-02T00:10:00Z', '33.3');
INSERT INTO temperatures VALUES ('Los Angeles', '2023-11-02T00:11:00Z', '64.9');

Here, I’m mapping temperatures for two different cities. For several minutes only New York reports temperatures, then Los Angeles also begins reporting. Each city checks in every two minutes: NY on the even minutes and LA on the odd minutes.

I would like to have a graph that shows a single timeline (in this case for 11/2/2023 from 12:00AM to 12:11AM) with a graph showing both cities on it and their temperature changes. This seems like a pretty common thing (comparable to showing, say, CPU usage changing on two different hosts over the same span of time).

But I cannot gather from the documents how to accomplish this. My SQL skill is somewhat middling, and a simple query of SELECT site, time, temp from temperatures ORDER BY time only produces tabular output and no graphs at all. Changing the query to SELECT site, time, temp from temperatures ORDER BY time gives a single graph that bounces back and forth, which isn’t what I want (and of course with no indication of site).

The docs seem to indicate that dimensions can be managed by a GROUP BY clause, but doesn’t seem to give any examples I can make use of. I tried SELECT site, time, temp from temperatures GROUP BY site, time ORDER BY time but that didn’t seem helpful.

Can anyone help me get started with this query? What I’d really like to do would be to have a single timeline that can show, say, the changing temperatures for 10 cities.

Thanks to anyone who can help!

[codlord]

I always use this sort of format for my SQL, selecting 3 fields and it seems to work for me. I always use the names “time”, “metric” and “value” in that order:

select MyDateTimeField as time,
TheFieldIWantToShow as metric,
TheValueField as value
from ...
where ...
group by metric
order by time

So in your case site is metric and temp is value.

However, your “time” field is a varchar not a date or date/time field, I am not familiar with SQLite but I think you are going to need to change that field to a valid date/time field or convert it to a valid date/time field within the SQL query for Grafana to work correctly. It needs a time to plot a time series…

Also, if you want to use Grafana date/time range picker for your graph you will need to do:

SELECT $__timeGroupAlias(yourdatetimefield, $__interval) as time,
…
ORDER BY $__timeGroup(yourdatetimefield,$__interval)

[yosiasz]

Welcome @dwhite21

SELECT site as metric,
 time, 
temp as value
from temperatures 
ORDER BY time 

Then go to tranformation tab then use convert to convert your time field to time as it is varchar in your sqlite.

What version of grafana are you on

[dwhite21]

Thanks for your reply. I’ve tried that out in conjunction with yosiasz’s suggestion below, but my graph still only seems to show the temperature for one city (now labeled simply as “value”) so I must still be missing something. (The time series seems OK, though - the SQLite plugin accepts either an epoch timestamp or VARCHAR dates in the format of YYYY-MM-DDThh:mm:ssZ and automatically converts them to “time” types).

[dwhite21]

yosiasz, thanks for your reply. I think the SQLite plugin is handling the conversion of YYYY-MM-DDThh:mm:ssZ varchar time formats properly (that’s one of its features), so with your query I now have a time series with the right timestamps on the X-axis.

But unlike your panel, mine only shows the values for a single city (New York) which is now labeled just as “value”. Is there something in addition to the SQL command I need to do to get a graph like yours, showing values from both cities and properly labeled in the legend?

This is Grafana v10.0.0

Thanks again for any help.

[yosiasz]

change this to time series

[dwhite21]

Good catch - thanks! I made one other change because I need to account for the fact that each city updates every other minute, with NY on even minutes and LA on odd minutes, and the graph should not show the city temperature dropping to 0 on the minutes when it does not update.

In the “Time Series” visualization config, I set Graph Styles → Connect null values to always, which seems to fix the problem. (It’s hard to see since the temperatures vary so little from minute to minute, but the lines do go up and down slightly)

But please let me know if this was the wrong way to go about fixing the problem.

And thanks again!

[yosiasz]

Are you really that interested in minute by minute temp change? That is why you see that almost flat line, kind of boring?

[dwhite21]

Oh no, this is just sample data I made up to try to make the simplest possible scenario for my question! The real data will be CPU, RAM, etc. But I wanted to account for the fact that some systems I’m planning to graph may give a data point every minute, some every 5 minutes, some every 15 minutes, etc. When that happens, I don’t want the graphs for the less-frequent systems to drop to 0 when they don’t check in as often as the every-minute servers. I’m hoping that “Connect Null Values” might be the right solution for that.