How to show date data in Graph correctly?

[CYY]

• What Grafana version and what operating system are you using?
  7.5.7

• What are you trying to achieve?
  Plot the data get from mysql db with correct date time

• How are you trying to achieve it? What happened?
  I’ve followed some tutorial and could plot the data to ‘Table’ from mysql db.
  But I want to plot the data with ‘Graph’, then I wrote the following SQL to get the data:

SELECT
  UNIX_TIMESTAMP(date) AS "time",
  `total` AS "total_num",
  `inform_num` AS "inform",
  `extra_num` AS "extra"
FROM covid_table

If I choose to draw with ‘Table’, it seems okay:

But if I choose to draw with ‘Graph’, it seems like x-axis’s data(date) is not correct:

And the value at y-axis is wrong as well.
(Maybe because x-axis’ data is incorrect(?

• What did you expect to happen?
  Correctly show date time at x-axis

• Did you follow any online instructions? If so, what is the URL?
  reference
  Originally, my ‘date’ data is VARCHAR format, therefore I use function UNIX_TIMESTAMP() to enable my original data format run with SQL, but the value is wrong.
  I stuck with this issue for a period of time, thus I decide to recreate a DB with ‘date’ data format as DATETIME, but it didn’t work either. So I’m wondering where did I do wrong…

The data format of my DB is shown below:

• date - DATETIME
• extra_num - INT
• home_inform_num - INT
• inform_num - INT
• total - INT

Any help or advice would be appreciated!

[pooh]

“I decide to recreate a DB with ‘date’ data format as DATETIME, but it
didn’t work either.”

This to me sounds like the correct thing to do. Please can you give more
details regarding “it didn’t work”?

What data does the Query Inspector show you is coming back from the database
when Grafana performs a query? Does this data look correct to you?

Antony.

[CYY]

Hi Antony,

Thanks for your quick reply!
Sorry for not making it clear, “it didn’t work” means the data show with “Graph” at x-axis didn’t show the right data as expected.
The data looks okay if show with “Table”, but the data show with “Graph” at x-axis doesn’t.
I suppose the data of x-axis should show the ‘date’ data with this SQL code(?
Here’s the query result:

And it seems like the “time” data show the same result with or without using UNIX_TIMESTAMP()
I’m not familiar with Grafana and SQL, did I miss some setting during this process?

[CYY]

Ahhhh I found out that it’s because I didn’t set the right time range when showing Graph…
Here if I change to six month it can show the date correctly

Thanks again for your help!
Take care and have a nice day!