$__timeGroup doesn't work correct. Grafana v8.2.6

mnovokshchenov · August 17, 2023, 2:35pm

Hello,

I need to group data by month using ‘created’ field for timeSeries. For this purpose I am using
$__timeGroupAlias(created,$__interval,0)
and
$__timeGroup(created,$__interval,0) + 14400 as time
The data are grouped but is not correct, e.g. part of January data is grouped in February. The table, graph view and query options on a screenshots below

My query is
SELECT
$__timeGroup(created,$__interval,0) + 14400 as time,
COUNT(*),
SUM(CASE WHEN status = ‘Verified’ THEN 1 END) as Verified,
SUM(CASE WHEN status = ‘Opened’ THEN 1 END) as Opened,
SUM(CASE WHEN status = ‘In Progress’ THEN 1 END) as In_Progress,
SUM(CASE WHEN status = ‘In Grooming’ THEN 1 END) as In_Grooming,
SUM(CASE WHEN status = ‘Closed’ THEN 1 END) as Closed
FROM jira_metric_record1
Where
$__timeFilter(created) AND
labels LIKE ‘%prod_defect%’
GROUP BY 1
ORDER BY 1
The question is how to define correct months grouping?

THANK YOU!!

~ the grafana team

clevernessisamyth · August 17, 2023, 2:45pm

Hello,

What is the number “14400”? What database are you using? Why did you set the “Interval” as “30d”, is the data get generated/written to the DB each 30 days?

mnovokshchenov · August 17, 2023, 2:54pm

Hello,

the “14400” was taken as workaround for __timeGroup according to the following discussion and it shows close to real data
$__timeGroup timezone issue for SQL data sources · Issue #51525 · grafana/grafana · GitHub
Using $__timeGroupAlias(created,$__interval,0) I got more discrepancies

Alias692×463 31.7 KB
the data base is MySQL
“Interval” as “30d” was calculated based on selected Min interval 1M.
Yes, it supposed to generate data in the end of each month. For now loaded data for 1 year.
the real records in table below

records744×250 17.8 KB

clevernessisamyth · August 17, 2023, 3:31pm

Oh the 14400 is -4 US timezone. Is that your timezone?

Did you try this

github.com/grafana/grafana

$__timeGroup should respect the browser time-zone to align groups

opened 09:20PM - 28 Jun 22 UTC

bminer

type/feature-request datasource/MySQL datasource/Postgres datasource/MSSQL

**What happened**: Time columns using `$__timeGroup` (and friends) are groupe…d according to UTC time boundaries. Example query: ```sql SELECT $__timeGroupAlias(created_at,$__interval,0), COUNT(*) AS "Created" FROM sales_order_lines WHERE $__timeFilter(created_at) GROUP BY 1 ``` - Assume `$__interval` is set to `12h` - Assume user selects time range between `2022-05-25 12:00:00 AM` and `2022-05-31 11:59:59 PM` - Assume user's timezone is US/Eastern -4:00 UTC Given this, Grafana converts this query to the following: ```sql SELECT UNIX_TIMESTAMP(created_at) DIV 43200 * 43200 AS "time", COUNT(*) AS "Created" FROM sales_order_lines SOL WHERE created_at BETWEEN FROM_UNIXTIME(1653451200) AND FROM_UNIXTIME(1654055999) GROUP BY 1 ``` Although `UNIX_TIMESTAMP` will correctly create 12-hour time buckets, the bucket boundaries will be rounded to UTC time boundaries. The time buckets will therefore be: | Database Raw Value | UTC +0:00 | US/Eastern -4:00 UTC | | ------------------ | --------- | -------------------- | | 1653436800 | 2022-05-25 00:00:00 | 2022-05-24 20:00:00 | | 1653480000 | 2022-05-25 12:00:00 | 2022-05-25 08:00:00 | | 1653523200 | 2022-05-26 00:00:00 | 2022-05-25 20:00:00 | | 1653566400 | 2022-05-26 12:00:00 | 2022-05-26 08:00:00 | | ... | ... | ... | | 1653912000 | 2022-05-30 12:00:00 | 2022-05-30 08:00:00 | | 1653955200 | 2022-05-31 00:00:00 | 2022-05-30 20:00:00 | | 1653998400 | 2022-05-31 12:00:00 | 2022-05-31 08:00:00 | | 1654041600 | 2022-06-01 00:00:00 | 2022-05-31 20:00:00 | Because of this, the Grafana client in the web browser (in US/Eastern) will render `2022-05-24 20:00` as the first time bucket, and while that contains the correct values for that 12-hour time bucket, the user might expect to see a time bucket starting at `2022-05-25 00:00`, as requested. Additionally, for the 7 days of data requested, the user will get 15 12-hour time buckets instead of the expected 14. **What you expected to happen**: Time columns should be grouped to respect the web browser's timezone. In our example, the first time bucket should start on `2022-05-25T04:00:00Z`, and the last time bucket should start on `2022-05-31T16:00:00.000Z`. (Note: these dates are in UTC time to avoid confusion) **How to reproduce it (as minimally and precisely as possible)**: This bug only occurs under these conditions: - `$__timeGroup` macro is used in a SQL query - The SQL database and web browser running the Grafana client have differing timezones - The time interval is larger than 1 hour - The timezone difference between the web browser and the database is not evenly divisible by the time interval (thus causing misalignment of the grouped time interval). In our example, since the database and web browser client timezones are 4 hours apart, `$__interval` of 2h and 4h works just fine. **Anything else we need to know?**: This is solely an issue with the macros `$__timeGroup`, `$__timeGroupAlias`, et. al. The `$__timeFilter` macro correctly filters data according to the correct timestamp. To be clear, the data returned by MySQL is correct and rendered correctly by Grafana; however, the time buckets are confusing to the end user under the conditions described above. **Environment**: - Grafana version: 9.0.1 - Data source type & version: MySQL 8.0.23; UTC timezone - OS Grafana is installed on: Ubuntu Linux 20.04 - User OS & Browser: Linux Mint 20.3; Firefox 101.0.1; US/Eastern timezone - Grafana plugins: Only pre-installed core plugins **Possible Solution**: I'm not quite sure how to solve this problem just yet, but I think a good start is sending the timezone offset information to MySQL to ensure grouping occurs properly. The `$__timeGroup(created_at, $__interval)` macro could then expand to: ```sql (UNIX_TIMESTAMP(created_at) + -14400) DIV 43200 * 43200 - -14400 ``` where `-14400` is the web browser's timezone difference from UTC in seconds and `43200` is the `$__interval` of 12h. **EDIT:** As discussed below, a better proposed solution is: ```sql (UNIX_TIMESTAMP(created_at) - 1653451200) DIV 43200 * 43200 + 1653451200 ``` where `1653451200` is the "from date" UNIX timestamp (the same value in the WHERE clause) and `43200` is the `$__interval` of 12h. A workaround for this might look like this: ```sql (UNIX_TIMESTAMP(created_at) - UNIX_TIMESTAMP('${__from:date}')) DIV ($__interval_ms / 1000) * ($__interval_ms / 1000) + UNIX_TIMESTAMP('${__from:date}') ```

CONVERT_TZ(FROM_UNIXTIME(
UNIX_TIMESTAMP(CONVERT_TZ(created_at, ‘UTC’, ‘US/Eastern’)) DIV ($__interval_ms / 1000) * ($__interval_ms / 1000)
), ‘US/Eastern’, ‘UTC’) AS time,

mnovokshchenov · August 17, 2023, 3:35pm

Thanks, applied your suggestion but got error: db query error: Error 1054: Unknown column ‘‘UTC’’ in ‘field list’
yes am in ET US time zone. The Timezone set Browser Time

yosiasz · August 17, 2023, 3:36pm

is your datetime column in your database UTC?

clevernessisamyth · August 17, 2023, 3:41pm

You forgot to change the single quotes from " ‘ " to " ' ".

mnovokshchenov · August 17, 2023, 4:04pm

Thanks! updated and got No data

and second one question, why following works as expected in outside Grafana for table

DATE_FORMAT(created, ‘%Y-%m’) as time,

but doesn’t work in Grafana. I tried
FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_FORMAT(created ,‘%Y-%m’))) AS time
but got only one record for whole year with weird date

mnovokshchenov · August 17, 2023, 4:28pm

clevernessisamyth · August 17, 2023, 4:29pm

The DATE_FORMAT(created, ‘%Y-%m’) doesn’t work in Grafana because it is a string, but Grafana needs a timestamp column.

You can use this to convert the type to datetime, see if it works:

SELECT STR_TO_DATE(DATE_FORMAT(created, '%Y-%m-01'), '%Y-%m-%d')

mnovokshchenov · August 17, 2023, 4:52pm

Thanks, it groups by month, but there is some offset in data about on 1month, e.g. data for January went to December previous year

the query is
SELECT
STR_TO_DATE(DATE_FORMAT(created, ‘%Y-%m-01’), ‘%Y-%m-%d’) as time,
COUNT(*) AS num_defects,
COUNT(CASE WHEN status = ‘Opened’ THEN 1 ELSE NULL END) AS opened_defect,
COUNT(CASE WHEN status = ‘Verified’ THEN 1 ELSE NULL END) AS verified_defect,
COUNT(CASE WHEN status = ‘In Progress’ THEN 1 ELSE NULL END) AS in_progress,
COUNT(CASE WHEN status = ‘In Grooming’ THEN 1 ELSE NULL END) AS in_grooming,
COUNT(CASE WHEN status = ‘Closed’ THEN 1 ELSE NULL END) AS closed_defect
FROM
jira_metric_record_year
WHERE
labels LIKE ‘%prod_defect%’
AND created >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY
time
ORDER BY
time;

yosiasz · August 17, 2023, 4:54pm

what data type is the created field? varchar, text, or actual time?

mnovokshchenov · August 17, 2023, 4:58pm

clevernessisamyth · August 17, 2023, 5:03pm

Is the data correct?

Because there is no timezone information stored with the timestamp, Grafana treats it as UTC and subtract 5 hours (US Eastern), so 2023-01-01 00:00:00 becomes 2022-12-31 19:00:00.

You need to convert the timezone within the query.

yosiasz · August 17, 2023, 5:09pm

That does not help us see the data type. if it is string data type (which it seems like it is) try

convert_tz(STR_TO_DATE(DATE_FORMAT(created, '%Y-%m-01'), '%Y-%m-%d'),'America/New York','UTC')

mnovokshchenov · August 18, 2023, 2:22pm

Hello,
I am sorry for late response, the line below brought only one entry in the table
convert_tz(STR_TO_DATE(DATE_FORMAT(created, ‘%Y-%m-01’), ‘%Y-%m-%d’),‘America/New York’,‘UTC’) as time,

yosiasz · August 18, 2023, 2:23pm

Have you selected time series for the Format or Table?

mnovokshchenov · August 18, 2023, 2:39pm

I only selected UTC for dashboard, I don’t have Format dropdown in my query section in Grafana

yosiasz · August 18, 2023, 2:42pm

Please show us the whole query as well as the resulting data of that query

mnovokshchenov · August 18, 2023, 2:54pm

My query is

SELECT
STR_TO_DATE(DATE_FORMAT(created, '%Y-%m-01'), '%Y-%m-%d) as time,
COUNT(CASE WHEN status = 'Opened' THEN 1 ELSE NULL END) AS opened_defect,
COUNT(CASE WHEN status = 'Verified' THEN 1 ELSE NULL END) AS verified_defect,
COUNT(CASE WHEN status = 'In Progress' THEN 1 ELSE NULL END) AS in_progress,
COUNT(CASE WHEN status = 'In Grooming' THEN 1 ELSE NULL END) AS in_grooming,
COUNT(CASE WHEN status = 'Closed' THEN 1 ELSE NULL END) AS closed_defect
FROM
jira_metric_record1
WHERE
labels LIKE '%prod_defect%'
GROUP BY
time
ORDER BY
time;

it bring following graph