Convert timestamps from SQLitetime colum for display

Grafana Time Series Panel
1

Hi,

I’m trying to create a query to display data. The basis is a SQLite DB, which has no official time stamp but is structured as follows: “YYYYMMDD HH: MM:SS”.

With the following query I get a time column in Grafana, but with all database values. It is not possible to limit the number of entries. The following query only works for ALL data:

“WITH converted AS (SELECT value, SUBSTR (TimeStamp, 1, LENGTH (TimeStamp) -13) ||” - “|| SUBSTR (TimeStamp, 5, 2) ||” - "|| SUBSTR (TimeStamp, 7, 2 ) || “T” || SUBSTR (TimeStamp, 10, 9) || “Z” as time from Tablexy) SELECT time, value FROM converted "

As soon as I want a restriction with:
“WHERE time> = $ __ from / 1000 and time <$ __ to / 1000”
no more dates are selected.

What am I doing wrong ? I am an absolute beginner …

2

I would recommend you try using the convert field type transformation. It will try to transform that field into a true timestamp:

3

These variables return milliseconds (UNIX timestamp). Since your time is a “string” you cannot filter by comparing against numbers. You need to convert your time to a UNIX timestamp (there are SQLite methods for that) or use some other filtering.