Grafana: $__timeGroup with dateadd()

Given a Grafana panel with the following (part of an) sql query on an SQL-server database:

SELECT $__timeGroup(DATETIME_FIELD, $aggr_interval)  -- $aggr_interval = '7d'
FROM MY_TABLE

which is translated into the following SQL query (according to the "Query inspector"):

SELECT FLOOR(DATEDIFF(second, '1970-01-01', DATETIME_FIELD)/604800)*604800
FROM MY_TABLE

Now, I'd like to add a number of seconds to the DATETIME_FIELD before passing it to $__timeGroup:

SELECT $__timeGroup(DATEADD(second, 10, DATETIME_FIELD), $aggr_interval)
FROM MY_TABLE

However, this results in a failure: interpolation failed: error parsing interval 10 even though the sql query would be acceptable to the sql server - if it would be generated. This is what should be generated:

SELECT FLOOR(DATEDIFF(second, '1970-01-01', DATEADD(second, 10, DATETIME_FIELD))/604800)*604800
FROM MY_TABLE

How to pass the DATEADD function as a parameter to $__timeGroup?

Grafana has trouble parsing content inside $__timeGroup: a comma inside your expression throws it off. As a result, it thinks that 10 is the second parameter of $__timeGroup. Most likely parentheses are not welcome too.

AFAIK, there is no way to escape the expression passed inside $__timeGroup. You'll need to avoid commas and parentheses.

The simplest way is to use an expression like DATETIME_FIELD + 1./24/60/6 instead DATEADD, if possible.

if treating DATETIME_FIELD as a number is not possible - you'll need to use a sub-query to prepare your data. For your example something along the lines of:

SELECT $__timeGroup(mdate, $aggr_interval) 
FROM (SELECT DATEADD(second, 10, DATETIME_FIELD) mdate
        FROM MY_TABLE)

EDIT:
Updated query for exact case of OP:

SELECT $__timeGroup(mdate, $aggr_interval) as time,
       count(*) as value,
       'Metric name' as metric
FROM (SELECT id, 
             DATEADD(second, 10, DATETIME_FIELD) as mdate from MY_TABLE
       WHERE $__timeFilter(DATETIME_FIELD)
)
GROUP BY $__timeGroup(mdate, $aggr_interval)
ORDER BY 1

Based on markalex's answer there is a solution using a subselect in the from clause, which prepares the data:

SELECT $__timeGroup(a.mdate, $aggr_interval)
FROM MY_TABLE
inner join (select id, DATEADD(second, 10, DATETIME_FIELD) as mdate from MY_TABLE) as a on a.id = MY_TABLE.id

The subselect is correlated with the main query with the id column so that the subselect provides one and the same record for every main query record, in order to achieve the goal of preparing a modified value for DATETIME_FIELD.

Bigger picture: Full Grafana panel query

Although not part of the posted question, $__timeGroup is often used as x-axis value in a Grafana time-series query. Here is how it's being used in a query that provides x-axis value, y-axis value and metric name:

SELECT $__timeGroup(mdate, $aggr_interval) as time, count(*), 'Metric name' as metric
FROM MY_TABLE
inner join (select id, DATEADD(second, 10, DATETIME_FIELD) as mdate from MY_TABLE) as a on a.id = MY_TABLE.id
WHERE $__timeFilter(DATETIME_FIELD)
GROUP BY $__timeGroup(mdate, $aggr_interval) order by 1

I had the same error, I created a new column to do the time_add.

Select 
  $__timeGroup(Date, '3m', 0) as time,
  count(rows),
  date_add(Date, interval 1 day) as time2
From table

To display the time series graph you have to add a transformation Organize fields and hide the time column.