Unable to use SQL CASE statement in Grafana when plotting a time series?

user_1359787 0
Viewed 278

Trying to use a SQL query in Grafana v8.3.3 to plot a time-series to alert if no orders recieved in a previous hour, but only during essentially business hours (i.e 8am - 10pm).

Since the alert can only be run hourly, I'm going to hack the SQL query to return 1 during non-business hours (i.e 11pm - 7am) so alert will not fire.

Tried using CASE but doesn't seem to see the value returned as being a number - any thoughts on how best to fix this, or implement the above in Grafana?

Thanks in advance!

Without CASE: Without CASE

SELECT $__timeGroup(DATE_CREATED, '1h', 0), 
1 AS "BOOKED" 
FROM CHANNEL_ORDER
WHERE STATUS = 'BOOKED'
GROUP BY $__timeGroup(DATE_CREATED, '1h', 0)
ORDER BY $__timeGroup(DATE_CREATED, '1h', 0)

With CASE With CASE

SELECT $__timeGroup(DATE_CREATED, '1h', 0), 
CASE WHEN TO_CHAR(DATE_CREATED,'HH24') >= 8 AND to_char(DATE_CREATED,'HH24') <= 22) THEN COUNT(1) ELSE 1 END) AS "BOOKED" 
FROM CHANNEL_ORDER
WHERE STATUS = 'BOOKED'
GROUP BY $__timeGroup(DATE_CREATED, '1h', 0)
ORDER BY $__timeGroup(DATE_CREATED, '1h', 0)
grafana case sql
1 Answers

Your case expression has two problems. First, you pass DATE_CREATED in as a parameter, but it's not in the GROUP BY, only $__timeGroup(DATE_CREATED, '1h', 0) is.

  • All references in the SELECT must either be in the GROUP BY or inside an aggregate function.

Second you convert the date to a string, and compare it to an integer; this comparison is not possible and so the SQL engine implicitly converts one of the two, based on datatype order of precedence, to make them the same datatypeq. In your case, the integer is converted to a string.

As the $__timegroup() macro return the time rounded as an integer number of seconds, I would instead suggest something like...

SELECT
  $__timeGroup(DATE_CREATED, '1h', 0), 
  CASE
    WHEN $__timeGroup(DATE_CREATED, '1h', 0) % 86400 <  8*60*60 THEN 1
    WHEN $__timeGroup(DATE_CREATED, '1h', 0) % 86400 > 22*60*60 THEN 1
    ELSE COUNT(*)
  END AS "BOOKED" 
FROM CHANNEL_ORDER
WHERE STATUS = 'BOOKED'
GROUP BY $__timeGroup(DATE_CREATED, '1h', 0)
ORDER BY $__timeGroup(DATE_CREATED, '1h', 0)

Note: I used % for modulo, as I'm inferring the your underlying database is PostgreSQL?

Also, bear in mind that your use of , 0 in the macro call means that any hours without any data will be assigned a result of 0, not 1. An alternative to a CASE expression could be...

SELECT
  $__timeGroup(DATE_CREATED, '1h', 1), 
  COUNT(*) AS "BOOKED" 
FROM CHANNEL_ORDER
WHERE STATUS = 'BOOKED'
  AND $__timeGroup(DATE_CREATED, '1h', 1) % 86400 >=  8*60*60
  AND $__timeGroup(DATE_CREATED, '1h', 1) % 86400 <= 22*60*60
GROUP BY $__timeGroup(DATE_CREATED, '1h', 1)
ORDER BY $__timeGroup(DATE_CREATED, '1h', 1)
Dems_533410
Related