'localtime' modifier is ignored on Date and Time

[iomax]

Describe the bug 'localtime' modifier is ignored on Date and Time functions as datetime() or strftime() ), other modifiers ( e.g.: '+H hours') are working as expected

To Reproduce

1. add new panel
2. select SQLITE as data source
3. in edit panel write: select datetime('now','utc') as UTC, datetime('now','localtime') as LOCAL
4. the result will be same on both, "UTC" and "LOCAL", columns ( UTC+2:00 timezone here )

Screenshots If applicable, add screenshots to help explain your problem.

Versions (please complete the following information):

• OS: Alpine Linux v3.15
• Run via docker? yes
• Browser : safari, firefox
• Grafana Version v9.3.8
• Plugin Version 3.1.2
• Plugin installed via grafana-cli or manually? grafana-cli

[fr-ser]

Run via docker? yes

Are you sure you are setting your "localtime" correctly inside docker? My current working theory is that the docker container does not set a local time at all.

[iomax]

Hi, thank for the reply ... yes it is ( TZ env var and confirmed via running the shell on the same container ). Any thing to try ?

[fr-ser]

So if you open the Sqlite database directly the query you posted returns a different result than when running the query via Grafana?

Could you provide a Dockerfile and/or docker compose file for your setup?

[iomax]

Regarding Grafana I'm using this https://hub.docker.com/r/grafana/grafana-oss image ( grafana/grafana-oss:9.3.8 image, tried also grafana/grafana-oss:9.3.8-ubuntu with the same result ) and, due to the missing sqlite3 on it, no option to run the query directly anyway yes, the query in my example show different, but expected, results if run in other containers. I arranged a minimal and dedicated sqlite3 container to play with it ( https://github.com/iomax/Dockerfile_of_SQLite3 ) and you can try it or even with any nodered official container image, of course with the additional sqlite node, too.

Coming back to the "my" grafana container I can confirm that ( Alpine ) timezone package is included and working in the container, quickly by the shell and standard "date" command. Playing around "TZ" env var ( e.g. TZ=Europe/Rome) you can see the different time with the plain "date" ( localtime) against "date -u" (UTC time) command.

Anyway thank again for you support and let me to arrange a minimal/dedicated Grafana and sqlite3 container, and I'll back to you.

p.s. sorry for my English, as clear I'm not English motherwtounge :-)

[iomax]

Ok, to try it please find below what I did :

$ docker run -d --name=grafana_lab --env TZ="Europe/Rome" -p 3000:3000 grafana/grafana-oss:9.3.8
$ docker exec -u 0 -it grafana_lab bash

bash-5.1# grafana-cli --pluginUrl https://github.com/fr-ser/grafana-sqlite-datasource/releases/download/v3.1.2/frser-sqlite-datasource-3.1.2.zip plugins install frser-sqlite-datasource
bash-5.1# apk --no-cache upgrade ; apk --no-cache add sqlite
bash-5.1# exit

$ docker restart grafana_lab
$ docker exec -it grafana_lab bash

bash-5.1# cd
bash-5.1# sqlite3

sqlite> .header on 
sqlite> .mode column
sqlite> .open test.db
sqlite> select datetime('now') as UTC, datetime('now', 'localtime') as LOCAL;

UTC                  LOCAL
-------------------  -------------------
2023-04-20 08:21:03  2023-04-20 10:21:03

Then I open Grafana, of course from the same container, create a new SQLite data source ( Path : /home/grafana/test.db ) and a new dashboard. Added a new panel in it with the same query.

[fr-ser]

Thank you for the test. I will check it out further myself, too, and see what the issue might be

[fr-ser]

I took a look and after a bit of a journey I arrived at the issue being part of a library I use. I created a bug ticket in their repository but cannot guarantee any follow up: https://gitlab.com/cznic/sqlite/-/issues/141.

In the meantime. Wouldn't it be better if the conversion to local time happens inside Grafana anyway instead of in the database layer?

[iomax]

Sergej I really appreciate your effort. Unfortunately Grafana way will be not an option in my case ( pretty complete query/join and need to aggregate by "correct day" ). Maybe in my case the feasible workaround will be some work on data source ( sort of store procedure, view or something like that ) to provide already localized data instead of utc. Sqlite newbe here so it's time to investigate deeply on doc :-)

[iomax]

Hi Sergej, I'm following your "ticket" on library repository and I've to say that your and they reaction was lightning fast .. wow !

Seems that a new library (temporary) version is already available for the test ... unfortunately I don't have any knowledge about "go" but I'll happy to help on test side if you can teach/address me on the steps to follow

[fr-ser]

I am also very positively surprised by the sqlite package maintainers.

I tested the go code and it looks good. I myself also don't know how I could test the grafana plugin with the yet unreleased go code. Once a version of the SQLite package is released with the fix I will create a release candidate version of the plugin that you can test 👌🏻

[fr-ser]

Feel free to test out if this new release fixes your issue: https://github.com/fr-ser/grafana-sqlite-datasource/releases/tag/v3.1.3-rc.1

It is only available via manual installation from Github, though.

[iomax]

Great !!! ... just a quick test but I can confirm that it works as expected :-)

Thanks.