Alert Query Not working with global variables

Grafana Alerting
1

Version: 9.1.7
Datasource: Postgres

I’m trying to setup a simple alert rule where I query data for a given time range e.g. last hour. I have tried using the global variables and functions to set the between statements and I get an error ‘Invalid Date’.

The same query does work when used in a panel.

If the alert query is updated with a hardcoded date range it does work. If I try resolving this with sql using built in functions again it does not work.

Should we be escaping special cases in the Alert rule query definition, if so how?

Below is an example query as a guide.

select Code, LatencyInNs
from ‘Events’
where Ts BETWEEN ‘2022-10-19T02:45:36.017Z’ AND ‘2022-10-19T14:45:36.017Z’

The above static statement works but if you replace the last line with:

where $__timeFilter(Ts)

It does not work.

2

What is “global variable”? Do you have any Grafana doc where is mentioned that “global variable” supported?

3

E.g above which can be used in queries

4

See PostgreSQL | Grafana documentation

$__timeFilter(dateColumn)	Will be replaced by a time range filter using the specified column name. For example, dateColumn BETWEEN FROM_UNIXTIME(1494410783) AND FROM_UNIXTIME(1494410983)

So that doesn’t produce what you need.

I would try:

WHERE Ts BETWEEN '${__from:date:iso}' AND '${__to:date:iso}'
5

I’ve already tried what you’ve suggested.

I’ve tried to control the format of the datetime generated but I still get an invalid date error.

AND ts BETWEEN ‘${__from:date:YYYY-MM-DDTHH:mm:ss.sssZ}’ AND ‘${__to:date:YYYY-MM-DDTHH:mm:ss.sssZ}’

When I inspect the query or add the query to a panel it runs successfully.

6

Also is there a way I can see the sql generated by the alert being triggered. I don’t mean the Inspect panel for the rule as I am getting different results when the same query is run from there.

For example is there any logging that would show me the query and how can I enable this?