Milliseconds Support

[UZ9]

Hi Sergej,

I've recently been using your SQLite data source plugin for Grafana and was wondering if the time formatted columns by any chance supported milliseconds. I have a bunch of data with less than second intervals, and unfortunately, Unix epoch (which worked out of the box, thank you!) doesn't support sub-second timestamps. If there happens to be another time format for this plugin capable of handling milliseconds I would be more than happy to know. Thank you!

[UZ9]

After quite a bit of fiddling and wondering why the RFC3339 times weren't being parsed correctly, I finally found the issue: I had the time column set to type timestamp, making the valueType check for STRING fail. Changing the column to instead be of type TEXT fixed this issue completely. Cheers!

[fr-ser]

Interesting. I will check that out later

[fr-ser]

Could you give me an example dataset? Database or CTE?

I am unsure right now if the issue was within the SQLite database or the plugin

[vsviridov]

I ended up writing two columns to my database, one in milliseconds, since that's what grafana uses to populate __from and __to for picking the date range, and another one in RFC3339 format, for using as the time column, to obtain correct millisecond level sorting.

[fr-ser]

since that's what grafana uses to populate from and to for picking the date range

You can just divide that number by 1000. I don't understand why you had to create a new column because of that

and another one in RFC3339 format, for using as the time column

RFC3339 also allows for using milliseconds. Was there a reason, why this was not possible in your case?

[vsviridov]

Because the column in RFC3339 format cannot be divided by a thousand. This format allows for your plugin to parse with milliseconds. If I store the time in milliseconds grafana interprets it as if it's in year 2745 or something, and if I divide it by a 1000 to get the correct date - I lose milliseconds and all records end up getting sorted incorrectly.

Grafana seemingly ignores ORDER BY and sorts on its own by the time field anyway...

[vsviridov]

Sorted by millisecond time divided by 1000

Sorted by RFC3339 time

notice that the POST /api/dashboard/sync-square-catalog 200 is not in the correct place when the / 1000 method is employed.

numTime column is essentilly unix epoch with milliseconds time is the same timestamp formatted as RFC3339

sqlite> select * from logs where json_extract(data, '$.requestId') = '72cbadc3-7083-4ef4-9205-664bd5c397dd' order by time asc limit 1;
┌──────────────────────────┬───────────────┬───────┬──────────┬─────────┬──────────┬────────┬─────────────────────────────────────────────┬──────────────────────────────────────────────────────┐
│           time           │    numTime    │ level │ numLevel │   pid   │ hostname │ module │                     msg                     │                         data                         │
├──────────────────────────┼───────────────┼───────┼──────────┼─────────┼──────────┼────────┼─────────────────────────────────────────────┼──────────────────────────────────────────────────────┤
│ 2021-08-18T01:29:56.290Z │ 1629250196290 │ info  │ 30       │ 3330040 │ iridium  │        │ <-- POST /api/dashboard/sync-square-catalog │ {"requestId":"72cbadc3-7083-4ef4-9205-664bd5c397dd"} │
└──────────────────────────┴───────────────┴───────┴──────────┴─────────┴──────────┴────────┴─────────────────────────────────────────────┴──────────────────────────────────────────────────────┘

[fr-ser]

Hmm. Even by dividing by 1000, you should not lose precision. I'll check if there is some integer conversion happening somewhere and maybe I can replace that with a float conversion.

It is nice that you found a workaround but I'll see if I can support this more naturally

[fr-ser]

Can you try out v2.1.1? https://github.com/fr-ser/grafana-sqlite-datasource/releases/tag/v2.1.1

[vsviridov]

I'll update and take a look.

[ghost]

Hello, I tried version 2.1.1 and 2.2.0 but still losing milliseconds, same issue as vsviridov

[fr-ser]

Hello, I tried version 2.1.1 and 2.2.0 but still losing milliseconds, same issue as vsviridov

Can you please expand on the issue and provide me a reproducible example? It looks to work fine on my end 🤷🏻

[ghost]

Hello, thanks for looking into this. Attached three images, top and middle are missing milliseconds, the middle image also shows when timestamp is not divided by 1000 the date time is completely off.

I was expecting something more like this (converted the last timestamp using EpochConverter):

[fr-ser]

Attached three images, top and middle are missing milliseconds

Can you show me how another plugin shows milliseconds? Currently, I think the milliseconds are processed correctly and Grafana simply removes them when converting the time to a string for the UI...

the middle image also shows when timestamp is not divided by 1000 the date time is completely off.

Unix timestamps are defined in seconds. You need to divide them by 1000 since Grafana provides them as milliseconds

[ghost]

I got the milliseconds to show by multiplying my 13 digit Unix timestamp by 0.001 and zooming in the plot. If you zoom out then the milliseconds don't show.

Left side of image shows when zoomed out milliseconds don't show, and right side shows when zoomed in milliseconds show. Thank you for looking into this! 🙏

I'm running Grafana version 8.3.3 and SQLite plugin 2.1.1