Help creating timeseries graph using mysql data

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:

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!

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.

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

Hi!

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

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.

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

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

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

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