PostgreSQL div/0 error on $__timeGroup fill when interval < 1s

In a timeseries panel with a PostgreSQL datasource, edit the query to this pretty simple SQL and it generates the dev/0 error.

SELECT $__timeGroup(‘2023-07-20’::TIMESTAMPTZ, ‘10ms’, NULL), 10

While this query does NOT generate the error

SELECT $__timeGroup(‘2023-07-20’::TIMESTAMPTZ, ‘1s’, NULL), 10

  • What Grafana version and what operating system are you using?
    9.2.1

  • What are you trying to achieve?
    Not connect NULL values, but filling with NULL causes error

  • How are you trying to achieve it?
    With the FILL parameter of the $__timeGroup macro

  • What happened?
    Panel errors out with “runtime error: integer divide by zero”

  • What did you expect to happen?
    Query return with NULLs for missing data - this works with >1s intervals

  • Can you copy/paste the configuration(s) that you are having problems with?
    SELECT $__timeGroup(‘2023-07-20’::TIMESTAMPTZ, ‘10ms’, NULL), 10

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    Query returns with error “runtime error: integer divide by zero”

We’ve tried several workarounds, like a CASE statement to not use the NULL fill param - but it turns out the $__timeGroup macro is evaluated before sending to the datasource.

So - fun test - even if your $__timeGroup query is COMMENTED OUT, it still throws the error… aka this fails

SELECT ‘2023-06-20’::TIMESTAMPTZ as “time”, 10
–SELECT $__timeGroup(‘2023-07-20’::TIMESTAMPTZ, ‘10ms’, NULL), 10

try this instead

select $__timeGroup(time, '5m', null) from (
SELECT '2023-07-20'::TIMESTAMPTZ as time
) a

Then click query inspector

image

And click the Query tab to see what the actual pg query looks like and post back?

@yosiasz Thanks for reading!

Set query source to your suggested

select $__timeGroup(time, '5m', null) from (
SELECT '2023-07-20'::TIMESTAMPTZ as time
) a

Got a few errors…

  • error parsing fill value null : Changed to NULL
  • no time column found : added as time on first line
  • data is missing a number field: added back my , 10 dummy value

Ended up with this, which I think is where you were going

select $__timeGroup(time, '5m', NULL) AS TIME, 10 from (
SELECT '2023-07-20'::TIMESTAMPTZ as time
) a

This doesn’t throw an error. Query inspection returns

select floor(extract(epoch from time)/300)*300 AS TIME from (
SELECT '2023-07-20'::TIMESTAMPTZ as time
) a

However, when I change the interval back from 5m to 10ms I am back to the
runtime error: integer divide by zero

I’m pretty sure this is a bug in the plugin that is happening before it is even sent to the database, as suggested by the error happening even on commented out code.

try that same query with the 10ms in PgAdmin, it wont work in pgadmin either. it is not a grafana issue.

select floor( ( extract(MILLISECONDS from time))/300)*300 AS TIME from (
SELECT '2023-07-20'::TIMESTAMPTZ as time
) a

But try this in grafana instead of using the $__timeGroup function

select floor( ( extract(epoch from time) * 1000.0)/300)*300 AS TIME from (
SELECT '2023-07-20'::TIMESTAMPTZ as time
) a