Time series with datasource sqlite

[pzwaag]

On a raspberry pi I have installed Grafana succesfully. A dashboard with monitoring data of the raspberry pi is already running (using telegraf and an influxdb).

However, for a few days I have been struggling with creating a time series of data from a table in a sqlite-database. I have read the instructions on SQLite plugin for Grafana | Grafana Labs) but still the message shows up: ‘Data does not have a time field’.

I have used the sql-query:

WITH CONVERTED AS
(SELECT dd, STRFTIME(‘%Y-%m-%dT%H:%M:%SZ’, time) AS datetime FROM TOTAAL WHERE stationname=‘D15-FA-1’)

SELECT datetime, dd FROM CONVERTED ORDER BY datetime ASC

In the table view data are shown correctly but unfortunately I have not been able to create a time series…

What have I done wrong?

Kind regards,
Pim.

[mattabrams]

Hi @pzwaag

it seems like grafana cannot recognize those values as timestamps. Can you format those values on the db side as unix epoch?

You can use the testDB datasource to test return values that grafana can recognize properly:

[pzwaag]

Thanks! You have given me the right direction. Now I am busy building my dashboard…

The datecolumn is converted to an integer instead of a string.