Add "Format as (Table | Time Series)" option

[DColadas]

Is your feature request related to a problem? Please describe. Some queries, such as those which bin timestamps, require boilerplate code to be used on the Timestream datasource. Take the following one:

SELECT BIN(time, 5m) time, host, AVG(used) value
FROM telegraf.disk
WHERE $__timeFilter
GROUP BY 1, 2

Although this is a fairly common construct, it requires a Prepare time series -> Multi-frame time series transformation to display properly on a Time series panel, and will throw an input data must be a wide series but got type long error if used in an alert rule.

Time series queries will be interpreted correctly when using the CREATE_TIME_SERIES function, but writing the equivalent query is trickier:

WITH binned_timeseries AS (
    SELECT BIN(time, 5m) time, host, AVG(used) value
    FROM telegraf.disk
    WHERE $__timeFilter
    GROUP BY 1, 2
)
SELECT host, CREATE_TIME_SERIES(time, value)
FROM binned_timeseries
GROUP BY 1

Describe the solution you'd like Having a Format as (Table | Time Series) option, like the one present in other SQL datasources (like the PostgreSQL or MySQL ones) would make these queries easier to write and more user-friendly for those not familiar with the datasource.

Describe alternatives you've considered n/a

Additional context n/a

[sarahzinger]

@DColadas thanks for the suggestion, we have prioritized this as a team and will try to look into implementing it over the next few weeks!