Search code examples
postgresqlgrafanatimescaledb

Grafana TimescaleDB last time bucket having a misleading value

In Grafana with timescale DB I have a timeseries graph, and the last time bucket always gets incomplete somehow. This seems like it should be the simplest thing to fix, but I cant figure out how.

How do I either ignore this latest time bucket, or fill in last sample or avoid this last incomplete time bucket sample?

It even follows me when changing the time range, so it is not that I get an incomplete time bucket at the end. Both these time ranges give me the same problem:

enter image description here enter image description here

How the problem looks like on the graph:

enter image description here

This is the Grafana TimescaleDB query I am using:

SELECT
  $__timeGroupAlias(observedat,$__interval),
  count(1) AS "count"
FROM observations_timescale
WHERE
  $__timeFilter(observedat)
GROUP BY 1
ORDER BY 1

This is the latest datapoints in table view, and the last one will slowly increase until it is at the level of the rest, and a new last time bucket will appear starting at 0 or 1.

time value
2023-04-21 15:53:00 217
2023-04-21 15:54:00 220
2023-04-21 15:55:00 180
2023-04-21 15:56:00 195
2023-04-21 15:57:00 1
Solution

  • Well, it seems to be the problem of time bucket grouping.

    To simply skip last time bucket you can use following query:

    SELECT *
FROM( 
  SELECT
    $__timeGroupAlias(observedat, $__interval),
    count(1) AS "count"
  FROM observations_timescale
  WHERE
    $__timeFilter(observedat)
  GROUP BY time
  ORDER BY time desc
  OFFSET 1
) t
ORDER BY time