Timegroup function starts on Thursday

Grafana
1

Hi,
I’m trying to figure out how to create a bar chart with weekly values based off a MySQL table containing daily values. Currently, I’m doing this:

SELECT
  $__timeGroupAlias(date, '1w'),
  sum(value)/100 AS value
FROM
  pvsum
GROUP BY
  1
ORDER BY
  $__timeGroup(date, '1w') DESC
LIMIT
  12

This gives me 7 day sums, however, due to the way timegroup works it is always Thursday to Wednesday. I want it grouped Monday to Sunday.
I have found a couple of threads adressing this issue but none seem to solve it.

Am I using a completely wrong approach, is the answer too obvious or is it not possible?

2

when you click on query inspector, what do you see, please post it

3

Query Inspector shows this:

SELECT
  UNIX_TIMESTAMP(date) DIV 604800 * 604800 AS "time",
  sum(value)/100 AS value
FROM
  pvsum
GROUP BY
  1
ORDER BY
  UNIX_TIMESTAMP(date) DIV 604800 * 604800 ASC
LIMIT
  12
4

Run that query in MySQL, what do you get?

5

I get the expected results which are also displayed in Grafana:

+------------+-------+
| time       | value |
+------------+-------+
| 1733356800 |  1.49 |
| 1733961600 | 12.62 |
| 1734566400 |  21.3 |
| 1735171200 | 38.41 |
| 1735776000 | 21.38 |
| 1736380800 | 35.82 |
+------------+-------+

Data in DB starts at Dec 11. I want it grouped starting 1733698800 (Monday, Dec 9).

Main question is: Can timegroup do this or do I have to create my own SQL statement?

6

so that answers the question that $__timeGroup won’t work for starting the week on Monday.

so you will need some mysql function that will grab the week’s worth of data from Monday to Sunday (if Monday is your start of week)

1 Like
7

I’m sure I made it more complicated than it needed to be but here is my solution. It returns the date of the week’s Monday and groups by it. In Grafana I’m using the Time Format Transformation to turn the date into just the week number.

SELECT
  str_to_date(concat(yearweek(date,1),'Monday'),'%x%v %W') as "time",
  sum(value)/1000 AS value
FROM
  pvsum
GROUP BY
  1
LIMIT
  12
1 Like