fr-ser / grafana-sqlite-datasource

Grafana Plugin to enable SQLite as a Datasource
Apache License 2.0
126 stars 17 forks source link

Importing date column (without time) is not possible #20

Closed icheered closed 3 years ago

icheered commented 3 years ago

I have an SQLite3 table with 3 columns:

I want to plot 'gainloss' against 'date' in grafana, but since date does not have a time-stamp it won't play nicely and doesn't want to plot at all. When I also import the 'index' and set both 'index' as 'date' as Time Formatted Columns it will display the gainloss values but the timescale is set to "1970-01-01 01:00:[INDEX VALUE]".

I hope the image makes it clear.

image

fr-ser commented 3 years ago

The plugin currently supports two formats for time:

  1. If the column is numeric (integer or float) it is assumed, that this is a "unix timestamp" -> this is what happens with your index, but you definitely don't want that
  2. If the column is a string it is assumed to be an RF3339 timestamp like "2006-01-02T15:04:05Z07:00"

What you might want to try is something similar to this:

WITH converted AS (
    SELECT Gainloss,  Date || 'T00:00:00Z00:00' AS datetime FROM gainlosstotal
)
SELECT datetime, Gainloss FROM converted ORDER BY datetime ASC
icheered commented 3 years ago

If the datetime is formatted with 'T00:00:00Z' it works like a charm!

Final code that works:

WITH converted AS (
    SELECT Gainloss,  Date || 'T00:00:00Z' AS datetime FROM gainlosstotal
)
SELECT datetime, Gainloss FROM converted ORDER BY datetime ASC
fr-ser commented 3 years ago

I am glad this helped. I will adjust the documentation of the plugin to be more explicit of what I support in the time formatted column :ok_hand: