Alert time window query returns wrong range of data

[shoeffner]

What happened: I am setting up an alert using the condition:

WHEN min() OF query(A, 90m, now-60m) IS BELOW 150

But the response when hitting Test rule shows:

{
  "firing": true,
  "state": "alerting",
  "conditionEvals": "true = true",
  "timeMs": "23.504ms",
  "matches": [
    {
      "metric": "num_new_game_sessions",
      "value": 0
    }
  ],
  "logs": [
    {
      "message": "Condition[0]: Query",
      "data": {
        "from": 1615194762612,
        "queries": [
          {
            "refId": "A",
            "model": {
              "format": "time_series",
              "group": [],
              "metricColumn": "none",
              "rawQuery": false,
              "rawSql": "SELECT\n  request_start_time_dt AS \"time\",\n  num_new_game_sessions\nFROM requestlog\nWHERE\n  $__timeFilter(request_start_time_dt)\nORDER BY request_start_time_dt",
              "refId": "A",
              "select": [
                [
                  {
                    "params": [
                      "num_new_game_sessions"
                    ],
                    "type": "column"
                  }
                ]
              ],
              "table": "requestlog",
              "timeColumn": "request_start_time_dt",
              "timeColumnType": "datetime",
              "where": [
                {
                  "name": "$__timeFilter",
                  "params": [],
                  "type": "macro"
                }
              ]
            },
            "datasource": {
              "id": 1,
              "name": "MySQL Snacking3"
            },
            "maxDataPoints": 0,
            "intervalMs": 0
          }
        ],
        "to": 1615196562612
      }
    },
    {
      "message": "Condition[0]: Query Result",
      "data": {
        "meta": {
          "executedQueryString": "SELECT\n  request_start_time_dt AS \"time\",\n  num_new_game_sessions\nFROM requestlog\nWHERE\n  request_start_time_dt BETWEEN FROM_UNIXTIME(1615194762) AND FROM_UNIXTIME(1615196562)\nORDER BY request_start_time_dt",
          "rowCount": 0
        },
        "series": [
          {
            "name": "num_new_game_sessions",
            "points": [
              [
                613,
                1615198612000
              ],
              [
                586,
                1615198655000
              ],
              [
                627,
                1615199035000
              ],
              [
                605,
                1615199051000
              ],
              [
                628,
                1615199453000
              ],
              [
                606,
                1615199453000
              ],
              [
                0,
                1615199865000
              ],
              [
                0,
                1615199871000
              ]
            ]
          }
        ]
      }
    },
    {
      "message": "Condition[0]: Eval: true, Metric: num_new_game_sessions, Value: 0.000",
      "data": null
    }
  ]
}

Note that the query constructed the correct time window (10:12:42 to 10:42:42) but the results contain data between 11:16 and 11:37.

This is independent of the timezone or the time ranges I tried, although for small time ranges the query is usually completely empty (~<30 minutes), even though the data has a data point roughly every 5 minutes.

This makes it impossible to exclude, say, the last 10 minutes for which the data usually shows 0 before having the right values.

What you expected to happen:

That the inbetween query returns data in between.

How to reproduce it (as minimally and precisely as possible):

See query above.

Anything else we need to know?:

Environment:

• Grafana version: Grafana v7.3.7 (1e26164)
• Data source type & version: mysql Ver 15.1 Distrib 10.3.27-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
• OS Grafana is installed on: Linux 4.19.0-14-amd64 Duplicate panel with 3 x colspan 3 panels will add not add to empty row #1 SMP Debian 4.19.171-2 (2021-01-30) x86_64 GNU/Linux
• User OS & Browser: macOS Catalina 10.15.7, Firefox 85.0.2 (64-bit)
• Grafana plugins: Worldmap Panel + native plugins
• Others: –

[ying-jeanne]

Hi @shoeffner, thanks for reporting the issue, it seems like the date in MySQL DB is not stored in UTC, that is the reason why retrieved data is in the wrong time range. We recommend always store the date info in DB in UTC or with timezone info to avoid such issues. https://stackoverflow.com/questions/19843203/how-to-store-a-datetime-in-mysql-with-timezone-info

if because for any reason that you could not change the way of storing data in DB, you could do a workaround to use macro $__timeFrom() and $_timeTo() and manually adding the timezone delta into the query.

[shoeffner]

Thank you, indeed it seems to be the case, though there as another field in the data which seems to be stored in UTC – however, I still had to offset now by 70 instead of 10 minutes to make it work... So that might not entirely be true. In either way, the results are predictable enough that I can work with it, so thank you very much for your help!