Help creating timeseries graph using mysql data

Grafana MySQL
,
1

Hi!

I have a MariaDB (mysql) connection to Grafana. Right now it only has example data in the table that I use but it shouldn’t matter. I want a simple graph with timeaxis and value (kwh) axis. It was very simple with my connection with Influxdb but I can’t get it to work with the data coming from mysql.

If I switch to a table view the data is coming in and is being displayed:

image
image947×697 65.1 KB

But If I try to use a time series nothing is displayed in the graph, no data points are shown. Any help would be appreciated, thanks!

image
image1094×823 63.4 KB

The data is coming from two columns in mysql. “kwh” which is a decimal (5,2). “date” which is a “date” data-type. Note that it is only date and not date-time. Does that matter?

I have Grafana v8.3.3 running as an addon in Home Assistant supervisor OS.

2

Not sure if it will help solve the issue, but is there any way you can format your date in the Unix timestamp format, like this?

image

3

Hi!

Yes, I can but it doesn’t help. I tried using the UNIX_TIMESTAMP function and then the data is converted like this:

image
image1117×765 78 KB

But when using this function in the timeline it then switches to saying that “Data is missing a time field”. If I remove the WHERE statement the error disappears but then I’m back with the view from the original post, that no data is being displayed in the graph.

image
image1059×810 63.5 KB

Any other ideas? I’m tearing my hair out… :scream:

4

Try changing the SELECT statement of your query to these 3 lines and see what changes:

SELECT
   kwh,
   UNIX_TIMESTAMP(date) as "time",
5

Hi, thanks for the suggestion. I tried it and it didn’t make any difference. I thought I had missed something obvious but since it appears I haven’t I just started from the beginning again. Created a new table in mysql and instead change the columns to be called “dateSTAMP” and “value” and now it all works. Don’t ask me why but it could be because of occupied keywords so you cant use “date”. Anyway, thanks!

Lesson learned, recreate table with new naming…

1 Like