Regarding dates manipulation in MySQL

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

Grafana v8.1.4 Ubuntu

  • What are you trying to achieve?

I am trying to render a graph that shows number of sales order by date in the range of day, week and month.

  • How are you trying to achieve it?

In 3 panels, I have been using MySQL code as follow for day, week and month respectively:

Panel 1:
SELECT
$__timeGroup(created_at, ‘1d’,0) AS “time”,
COUNT(id) as value
FROM order
GROUP BY $__timeGroup(created_at, ‘1d’)
ORDER BY 1

Panel 2:
SELECT
$__timeGroup(created_at, ‘1w’,0) AS “time”,
COUNT(id) as value
FROM order
GROUP BY $__timeGroup(created_at, ‘1w’)
ORDER BY 1

Panel 3:
SELECT
$__timeGroup(created_at, ‘1M’,0) AS “time”,
COUNT(id) as value
FROM order
GROUP BY $__timeGroup(created_at, ‘1M’)
ORDER BY 1

  • What happened?

The data showed inaccurate result. From MySQL, The earliest data in table order is 2021-09-06 and the latest data is 2021-10-01.

There are 389 rows of data right now, using the command “SELECT * FROM order WHERE created_at BETWEEN ‘2021-09-01’ AND ‘2021-09-30’ ORDER BY created_at DESC” directly via MySQL.

However, panel 3 shows 71 are from August and 318 are from September.

  • What did you expect to happen?

I expect all data to be shown in September for panel 3.

In fact, for panel 2, I would like the weeks to start counting data from sunday to saturday for each week.

For panel 3, I would like each month to start counting from 1st day to the last day of that month.

Are these possible via Grafana syntax?

  • Can you copy/paste the configuration(s) that you are having problems with?

See above.

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

No.

  • Did you follow any online instructions? If so, what is the URL?

No.