MySQL foun row with no time value

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

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

Thank you for your help!

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

This one return 0

image

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

@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.

If I use

timestamp as time_sec,

the result is corect (like in second picture):

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

The datatype is varchar(50)

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

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

and in the grafana I will have:

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

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.

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