MySQL foun row with no time value

Grafana MySQL
1

Hello
Please give me an advise regarding this error.

I have tried to receive data from mysql database.
In that table I have next columns: timestamp ; masa ; id

For generating codes I use :

  SELECT
  UNIX_TIMESTAMP (timestamp) as time_sec,
  masa as value,
  timestamp as metric
 FROM dbo.masa_baterii
 where timestamp != 0
ORDER BY timestamp ASC

And the Format as Time series

In this case I receive de error : Found row with no time value.

If I put that code in MySQL Workbench and I run it, I will observe:

Columns name: time_sec ; value ; metric with no values.

If I put the code :

  SELECT
  UNIX_TIMESTAMP (timestamp) as time_sec,
  masa as value,
  timestamp as metric
 FROM dbo.masa_baterii
 where timestamp = 0
ORDER BY timestamp ASC

I obtain values but at time_sec will be only 0.000000

If I put the code :

SELECT
  timestamp as time_sec,
  masa as value,
  timestamp as metric
 FROM dbo.masa_baterii
 where timestamp = 0
ORDER BY timestamp ASC

I obtain values in all columns

image
image469×702 25 KB

Please tell me what I can do to resolve this problem ?

Thank you for your help!

2

Found row with no time value means your query returned NULL in the time_sec column which is different from 0.

What does the following query return:

SELECT count(*) FROM dbo.masa_baterii WHERE timestamp IS NULL
3

This one return 0

image
image578×647 15.6 KB

4

can you leave out the metric column or set it to a fixed value. Something like the following query:

SELECT
  UNIX_TIMESTAMP(timestamp) as time_sec,
  masa as value,
  'value' as metric
 FROM dbo.masa_baterii
ORDER BY timestamp ASC
5

@svenklemm, I have leaved out the metric column but I guess the problem is on the first line

UNIX_TIMESTAMP(timestamp) as time_sec,

when I try to run this query in MySQL Workbench, the result is 0.000000 at “time_sec” column.

image
image364×703 20.1 KB

If I use

timestamp as time_sec,

the result is corect (like in second picture):

image
image434×699 24.9 KB

6

What datatype are you using for the the timestamp column in the masa_baterii table?

7

The datatype is varchar(50)

image
image933×176 10.9 KB

8

Ok thats the problem then. The mysql grafana datasource expects an integer value in the timesec column but unix_timestamp needs a datetime or timestamp datatype to work. So for your query to work you need to convert it to datetime first.
Something like the following query should get you closer to what you need

SELECT 
  UNIX_TIMESTAMP(STR_TO_DATE(timestamp,'%M %d %Y %h:%i:%s%p')) as time_sec, 
  masa as value,
  'value' as metric
FROM dbo.masa_baterii
ORDER BY timestamp

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date

9

@svenklemm thank you for prompt reply.
If I put the code as you tell it return the next situation:

image
image637×707 25.6 KB

and in the grafana I will have:

image
image710×906 32.7 KB

I m newbie on the sql field and I appreciate yours help.
Thank you !

10

OK try this query

SELECT 
  UNIX_TIMESTAMP(STR_TO_DATE(timestamp,'%M %d %Y %h:%i%p')) as time_sec, 
  masa as value,
  'value' as metric
FROM dbo.masa_baterii
ORDER BY timestamp

You might have to modify '%M %d %Y %h:%i%p' to match your date but the STR_TO_DATE must be able to parse your timestamp string, after that you can convert it to unix timestamp so grafana can process it.

11

It works !!!
Thank you very much for help!