Mysql : Found row with no time value

Hi all,

i created a graph ‘single stats’, everything is ok when in my request MYSQL find a value to display. But if my request find nothing, i have this message : “Found row with no time value”

Climbing_time --> datetime

here my request :

SELECT UNIX_TIMESTAMP(opening_time) as time_sec, count(id) as value, id as metric FROM communication WHERE (opening_time BETWEEN DATE_SUB(CURDATE(), interval 24 HOUR) AND DATE_ADD(CURDATE(), interval 7 HOUR)) AND (status = 'OUVERTURE' OR status = 'INFORMATION' OR status = 'OPÉRATION')

some people can help me ?

Hi,

So what do you expect to happen instead?

Marcus

Hi,

Example with a pie graph.
i have two query :
one query is “some information send before XX min”
second query is “some information send after XX min”

if in my two query i have information, it’s OK my graph appears

But if one query is empty :
data :
first query : 5 100%
second query : 0

my graph not appears and i have the message : “Found row with no time value”

I would like my graph appears everytime even if i have just one query with data (so pie chart 100%)
image

Hi,

I guess you need to provide a default zero/null row then?

Marcus

yes, you’re right, how can do it ?

for a better understanding

1 first query --> often 100%

SELECT
UNIX_TIMESTAMP(climbing_time) as time_sec,
count(c.id) as value,
c.id as metric
FROM YY i
INNER join XX c ON (i.id = c.incident_id)
where (c.climbing_time between DATE_ADD(CURRENT_DATE, INTERVAL(- WEEKDAY(CURRENT_DATE)-7) DAY) and DATE_ADD(CURRENT_DATE, INTERVAL(- WEEKDAY(CURRENT_DATE)-2) DAY)
AND (c.date - INTERVAL 10 MINUTE < c.climbing_time)

2nd query --> sometimes data

SELECT
UNIX_TIMESTAMP(climbing_time) as time_sec,
count(c.id) as value,
c.id as metric
FROM YY i
INNER join XX c ON (i.id = c.incident_id)
where (c.climbing_time between DATE_ADD(CURRENT_DATE, INTERVAL(- WEEKDAY(CURRENT_DATE)-7) DAY) and DATE_ADD(CURRENT_DATE, INTERVAL(- WEEKDAY(CURRENT_DATE)-2) DAY)
AND (c.date - INTERVAL 10 MINUTE > c.climbing_time)

Maybe you can use a union of two select statements, but unsure if that’s supported in Grafana. First select statement same as 2nd query for example. Second select statement default values if first where clause are false.

There’s probably other ways of solving this, using MySQL subqueries for example.

Marcus

do you have an example for union ? i don’t know how is work. i think that can help me
Thanks for your time

See for example https://dev.mysql.com/doc/refman/5.7/en/union.html

Marcus

the union works :slight_smile: thanks a lot