Converting Date issue

[waltervl]

I am not able to get graphical data out of my SQLite db. It is my first time experience with Grafana so it could be just my lack of knowledge...

I have a table Temperature with weather measurements. I have 2 issues:

1. Grafana/SQLite Plugin is not converting the Date value into a good value when using query SELECT Date, Temperature FROM Temperature Where DeviceRowID=213 and also set the Date column to be converted to time. What date conversion query I have to do?
2. If I remove the Date column as time formatting column I get a table with values but the temperatures (FLOAT) are rounded to integers (al rounded to 19 in below example). How to change this?

SQLite Table create statement: CREATE TABLE [Temperature] ([DeviceRowID] BIGINT(10) NOT NULL, [Temperature] FLOAT NOT NULL, [Chill] FLOAT DEFAULT 0, [Humidity] INTEGER DEFAULT 0, [Barometer] INTEGER DEFAULT 0, [DewPoint] FLOAT DEFAULT 0, [SetPoint] FLOAT DEFAULT 0, [Date] DATETIME DEFAULT (datetime('now','localtime')))

Example values: rowid	DeviceRowID	Temperature	Chill	Humidity	Barometer	DewPoint	SetPoint	Date
2706759	213	19.0	0.0	0	0	0.0	0.0	2021-01-03 17:15:00
2706781	213	19.0	0.0	0	0	0.0	0.0	2021-01-03 17:20:00
2706803	213	18.8	0.0	0	0	0.0	0.0	2021-01-03 17:25:00
2706825	213	18.8	0.0	0	0	0.0	0.0	2021-01-03 17:30:00
2706849	213	18.8	0.0	0	0	0.0	0.0	2021-01-03 17:35:00
2706874	213	18.8	0.0	0	0	0.0	0.0	2021-01-03 17:40:00
2706899	213	18.8	0.0	0	0	0.0	0.0	2021-01-03 17:45:00
2706924	213	18.8	0.0	0	0	0.0	0.0	2021-01-03 17:50:00
2706949	213	18.7	0.0	0	0	0.0	0.0	2021-01-03 17:55:00
2706974	213	18.7	0.0	0	0	0.0	0.0	2021-01-03 18:00:00

Grafana result: Date | Temperature 2021-01-03 17:20:00 +0000 UTC 19 2021-01-03 17:25:00 +0000 UTC 19 2021-01-03 17:30:00 +0000 UTC 19 2021-01-03 17:35:00 +0000 UTC 19 2021-01-03 17:40:00 +0000 UTC 19 2021-01-03 17:45:00 +0000 UTC 19 2021-01-03 17:50:00 +0000 UTC 19 2021-01-03 17:55:00 +0000 UTC 19

[fr-ser]

Regarding formatting a date as time take a look at this issue (link). The same problem (of understanding) occurred here and the solution should fit you as well if I understood correctly.

Regarding your dashboard problem, I don't understand the issue yet. Could you export your dashboard as JSON and upload a sample SQLite as well so I can take a look? Converting floats to integers should not happen so I would need to take a look at your SQL query (part of the dashboard)

[waltervl]

Thank you for the quick response, but my date conversion issue is a little bit different as there is already a time in the record. So I think I will have to convert 2021-01-03 17:15:00 into something like 2021-01-03T17:15:00Z to have Grafana accept it. But what is the SQL statement to do so? Or can your plugin do the conversion?

[fr-ser]

The plugin only supports the time formats mentioned here. I would recommend using SQLite functions to convert your string to a Unix epoch and just use that one.

[waltervl]

Problem is that it is not my database but filled by an external application. So conversion has to be done in Grafana.... I will try some SQL statements

[fr-ser]

Sure, you can use SQLite function in your query of course. The time format issue should not be a hard problem to solve.

The rounding part is something I cannot understand yet.

[waltervl]

I succeeded in Converting the date with below query and have datetime set as time formatted column

WITH converted AS ( SELECT Temperature, REPLACE(Date, ' ','T') || 'Z' AS datetime FROM Temperature WHERE DeviceRowID=213 ) SELECT datetime, Temperature FROM converted ORDER BY datetime ASC

The rounding part is only in table view, the graphs is showing floating values...

[ssnkhan]

I am struggling to get this working -- my time is stored in the following format: 2021-03-05 10:34:02.214543. This is what I have so far:

SELECT
strftime('%s', created_at) as time,
    country_code AS metric,
    count(*) AS value
FROM artifacts
WHERE  $__timeFilter(strftime('%s', created_at))
GROUP BY
    metric
ORDER BY
    value DESC;

[waltervl]

I think you will have to modify your time into the right format, REPLACE(Date, ' ','T') || 'Z' AS time

[ssnkhan]

Thank you,

strftime('%s', created_at) as time, does seem to be converting it to unix epoch time (one of the supported formats), but I am still getting a "not enough args to execute query: want 1 got 0" error.

I am struggling to understand the REPLACE syntax, as I am unfamiliar with it. Will that work given that I do not have a T separator in my datetime format?

[waltervl]

If column created_at contains the date time then you should do REPLACE(created_at, ' ','T') || 'Z' AS time

It will replace the space between date and time with a T and add the Z on the end resulting in a dateTtimeZ value.

[ssnkhan]

Hmmm, still getting the same error. This is my query now:

SELECT
REPLACE(created_at, ' ','T') || 'Z' AS time,
    country_code AS metric,
    count(*) AS value
FROM artifacts
WHERE  $__timeFilter(created_at)
GROUP BY
    metric
ORDER BY
    value DESC;

Sample time with replacement shows 2021-03-05T10:34:02.218346Z.

[waltervl]

I am no SQL programmer but why the Where statement and why the Order by on a DESC value that is not in the select statement? And metric should be a string, so if the country code is an integer it will fail too.

[fr-ser]

$__timeFilter(created_at)

This is a Grafana function and not supported by the plugin. What is supported though are the regular Grafana Variables. What I use is this (with Unix timestamps)

WHERE time > $__from / 1000 and time <= $__to / 1000

[ssnkhan]

I am no SQL programmer but why the Where statement and why the Order by on a DESC value that is not in the select statement? And metric should be a string, so if the country code is an integer it will fail too.

I am using the Grafana World Map plugin (https://grafana.com/grafana/plugins/grafana-worldmap-panel/). This plugin requires a country code (as metric), and a corresponding count (as value). The value is in the select statement.

I have tried to get the map working without the WHERE clause, but it seems it does need time series data. Let me try with @fr-ser suggestion and I will report back. Thanks for all your help!

[ssnkhan]

Still struggling to get this working. In the query inspector I can see that the correct columns are being returned: time, metric and value:

The query I have cobbled together is:

SELECT
    strftime('%s', created_at) AS time,
    country_code AS metric,
    count(*) AS value
FROM artifacts
WHERE strftime('%s', created_at) > 0
GROUP BY
    metric
ORDER BY
    value DESC;

I have used strftime('%s', created_at as this returns a unix epoch integer. This plugin seems to need a WHERE clause, so I have added that in to try and get the query working. In reality, I want the map to return all entries. I have not defined time as as "Time formatted column" as this changes the unix epoch back to datetime.

Will post over on the World Map issue page to see if they can shed any light. Thanks for your help!

[fr-ser]

This plugin seems to need a WHERE clause

I doubt that.

I have not defined time as "Time formatted column" as this changes the UNIX epoch back to DateTime.

You should, however. Only with this UNIX epoch is (in Grafana) treated as a Time column.

Also, I see, that above the query you chose Table as the return type. You might want to try Timeseries and see if the world map plugin prefers this.

[fr-ser]

Times series in Grafana are a tricky thing (each data source implements their own I guess :roll_eyes: )

The worldmap plugin seems to be especially unforgiving in this case. In the end what made my query work (aside from using time series as return type) was the title case of the column names (unbelievable):

[ssnkhan]

Thank you - feel like progress is being made! The query I now have is:

SELECT
REPLACE 
    (created_at, ' ', 'T') || 'Z' AS time,
    country_code AS metric,
    count(*) AS value
FROM artifacts
GROUP BY
    metric
ORDER BY
    time ASC

I no longer see any errors, but notice that the data being returned is not at all what I expect:

Specifically, the metric column is now missing, and the value is not populating.

[ssnkhan]

Times series in Grafana are a tricky thing (each data source implements their own I guess 🙄 )

The worldmap plugin seems to be especially unforgiving in this case. In the end what made my query work (aside from using time series as return type) was the title case of the column names (unbelievable):

Unbelievable - just changed them to sentence case and it is finally working again! Please may I buy you a beer or two!

[pnmice]

I am trying to get time series, but no luck.

select order_filled_date,price from orders; price FLOAT, order_filled_date DATETIME,

Table order_filled_date price 2022-01-27 21:10:01.834867 +0000 UTC 2.02

STRING

WITH CONVERTED AS (SELECT price, REPLACE(STRFTIME('%Y-%m-%dT%H:%M:%S', order_filled_date),' ','T') || 'Z00:00' AS Time FROM orders) SELECT Time, price FROM CONVERTED ORDER BY Time ASC

Time price 2022-01-27T21:10:01Z00:00 2.02

When I switched to time series. I got an error: "can not convert to wide series, expected long format series input but got not series"

The same problem with integer.

INTEGER

WITH CONVERTED AS (SELECT price, CAST(STRFTIME('%s', order_filled_date) as INTEGER) AS Time FROM orders) SELECT Time, price FROM CONVERTED ORDER BY Time ASC

Time price 1643317801 2.02

Grafana v8.3.4 (a551d74b11) frser sqlite 2.2.1

Can you give me a hint?

[pnmice]

Found problem. Time formatted columns were not "Time" tag