Date column not queried in SQLite

Grafana Dashboards
1
  • What Grafana version and what operating system are you using?

Grafana docker image

  • What are you trying to achieve?

Query SQLite database to retrieve time-based events and plot the corresponding values

  • How are you trying to achieve it?

Using the query

select
  started_at, 
  pst_id
from consolidated_results
group by pst_id
  • What happened?

The column started_at is empty. The other columns are correct.

  • What did you expect to happen?

The column started_at to have data such as 2022-11-09 08:25:13+00:00

  • Notes
    • The same query done directly on the SQLite file with DB Browser for SQLite returns correct data.
    • started_at is of DATETIME type.
    • I am not sure yet that the format is correct for Grafana (I read the the UNIX epoch is better) - right now the date is not being pulled in at all so I would like to fix that first (and as I type this I hope it is not related)
2

Ha. The format actually matters.

When changing my query to

select
  cast(strftime('%s', started_at) as integer) as date, 
  pst_id
from consolidated_results
group by pst_id

the data is correctly retrieved and plotted after hinting that the time column is date.

1 Like