I’ve had a ton of fun while getting into Grafana throughout the last weeks but also had my first touchpoints with the EPOCH-Timeunit. I’m currently using the SQL Builder within Grafana and the resulting Query always converts the Timestamp (Date & Time with time beeing 00:00:00) to an epoch-integer.
Here comes the issue which I’ve encountered a few times and which seems to be systematic in a way. The conversion seems to fail for some cases in a sense that the value of the current month is equal to the prior month’s value (please see pictures below). I’d understand this for edgecases being last day of a month or first day of the month but 2022-05-31 vs. 2022-06-09 being the same doesn’t make sense to me.
The picture is the result of a slightly modified SQL which also includes the actual “datum”/“time”-column from the source. Please also find my generated SQL below.
Some more additional details:
I’m using RasPi 4 / Ubuntu 21.10 with Grafana 8.5.3. The datasource is a PSQL Database which is being filled by a Pythonscript. All this is running within different dockervolumes on the RasPi.
The initial goal was to visualize the metric “kategorie” per month with the value being in column “betrag” and the timestamps being in column “datum”.
This has to be filtered to a specific “kategorie” and “av_identifier”.
SELECT floor(extract(epoch from datum)/2.6784e+06)*2.6784e+06 AS "time", kategorie AS metric, sum(-betrag) AS "betrag" FROM haushaltsbuch WHERE datum BETWEEN '2018-07-04T17:02:14.82Z' AND '2022-07-04T17:02:14.82Z' AND kategorie = 'Versicherungen' AND av_identifier = 'pension' GROUP BY 1,2 ORDER BY 1,2
Adjusted SQL to create the upper image just fyi:
SELECT datum AS "time", floor(extract(epoch from datum)/2.6784e+06)*2.6784e+06 as "epochtime", kategorie AS metric, sum(0) AS "betrag" FROM haushaltsbuch WHERE datum BETWEEN '2018-07-04T16:39:16.655Z' AND '2022-07-04T16:39:16.655Z' AND kategorie = 'Versicherungen' AND av_identifier = 'pension' GROUP BY 1,2,3 ORDER BY 1,2
I’m looking forward to any advice on how to fix this.