A relational query renders fine in Grafana's table view, but the Time series panel reports that the data is missing a time field. Explain what Grafana needs from a SQL data source to draw a time series, and how that differs from what a metrics store or a document store returns.

middleshould knowfreq 36%

answer

all sources → typed data frames; who supplies the type differs
SQL needs: time-typed column + Format as Time series + ascending order
$__timeFilter or the time picker is ignored (and the scan is unbounded)
$__timeGroup buckets to $__interval, with a fill option for gaps
long vs wide frames explains 'one series instead of five'

basics

~20 s

Every source returns data frames. Metrics stores are typed, so time and labels are known. SQL is schema-on-read: Grafana needs a column typed as time, a numeric value column, and the time-filter macro so the dashboard's range reaches the WHERE clause.

solid answer

~60 s

All data sources return data frames — typed columns. The difference is who supplies the types.

A metrics or trace store has a fixed model: the plugin knows which column is time and which dimensions are labels, so a time series is produced automatically. A document store is configured at data-source level — you name the time field and the index pattern once — and queries are built as time-bucketed aggregations, so the time field again comes from configuration.

A relational source has no such model. Grafana must be told:

A column typed as time (a real timestamp, or an epoch column converted). A string that looks like a date will not do.
Format as: Time series rather than Table, with the result shaped as time, optional metric name, then value — sorted ascending.
The $__timeFilter(column) macro in the WHERE clause, otherwise the query ignores the dashboard time picker entirely, and $__timeGroup(column, $__interval) to bucket at the dashboard's resolution.

Without the time filter the query is also unbounded — correct-looking and expensive.

All data sources return **data frames** — typed columns. The difference is who supplies the types. A metrics or trace store has a fixed model: the plugin knows which column is time and which dimensions are labels, so a time series is produced automatically. A document store is configured at data-source level — you name the time field and the index pattern once — and queries are built as time-bucketed aggregations, so the time field again comes from configuration. A relational source has no such model. Grafana must be told: 1. A column **typed as time** (a real timestamp, or an epoch column converted). A string that looks like a date will not do. 2. **Format as: Time series** rather than Table, with the result shaped as time, optional metric name, then value — sorted ascending. 3. The **`$__timeFilter(column)`** macro in the WHERE clause, otherwise the query ignores the dashboard time picker entirely, and **`$__timeGroup(column, $__interval)`** to bucket at the dashboard's resolution. Without the time filter the query is also unbounded — correct-looking and expensive.

code

text · 11 lines

text

SELECT $__timeGroupAlias(ts, $__interval, 0),
       service            AS metric,
       avg(latency_ms)    AS value
FROM   requests
WHERE  $__timeFilter(ts)
GROUP  BY 1, 2
ORDER  BY 1

-- $__timeFilter(ts) expands to a bounded predicate on the dashboard range
-- $__timeGroup buckets to the computed interval; the 0 fills gaps with zero
-- shape: time | metric(string) | value(number)  -> one series per service

go deeper

for a junior

Know that a time series panel needs a time-typed column and a numeric column, and that relational queries must use the time-filter macro and the Time series format mode.

for a middle

Contrast self-typing metrics stores, configuration-typed document stores and query-typed relational sources, and explain the time-group macro and its fill option.

for a senior

Use the panel inspector's field types and expanded query as the diagnosis path, and connect bucketing to max data points and interval so wide ranges stay cheap.

for a principal

Decide where shaping belongs: ad-hoc macro-laden SQL in every panel versus database views or pre-aggregated tables that make the correct shape the default across the estate.

One target shape: data frames

Whatever the backing system, a Grafana data source returns data frames: named fields with declared types (time, number, string, boolean) plus labels and display metadata. A time series panel needs a field of type time and at least one number field. "Data is missing a time field" means exactly that — no field arrived with the time type — and everything else follows from who is responsible for supplying it.

Metrics and trace stores: typed by the model

A metrics store has a fixed data model: a series is identified by dimensions and carries (timestamp, value) pairs. The plugin therefore knows, without being told, which part of the response is time, which is the value, and which are labels. It emits well-typed frames automatically, which is why these sources "just work" in a time series panel and why the panel can label series from dimensions without configuration. Log and trace stores are similar: the response shape is known to the plugin, which is what lets Grafana attach specialised behaviour such as turning a field into a link to a trace.

Document stores: typed by data-source configuration

A schema-flexible document store has no single model, so the data source configuration carries the missing information: which field holds the timestamp, and which index or index pattern to read. The query editor then builds bucketed aggregations — a date histogram over that configured time field, with sub-aggregations for the metric and for grouping terms — so the frames are again well-typed without the query author naming the time column each time. The corollary is that misconfiguring the time field at data-source level breaks every query built on it, and changing it is a data-source-wide change.

Relational sources: typed by you

A relational database can return any shape at all, so Grafana requires explicit cooperation.

A time-typed column. The column must arrive as a timestamp type. A varchar holding an ISO string, or a bigint of epoch seconds, is not a time field until it is converted — either cast in SQL, wrapped in the relevant epoch macro, or converted afterwards with a Convert-field-type transformation. This is the most common cause of the error in the question: the table view is happy to display strings, so everything looked fine there.

The Format-as choice. The query editor offers Time series, Table and, for some engines, Logs. Table passes columns through as-is. Time series asks Grafana to interpret the result as series: a time column, an optional string column naming the metric/series, and one or more numeric value columns, ordered by time ascending. Returning columns in an unexpected order, or leaving the result unsorted, produces either an error or a graph that zig-zags backwards.

The macros. These are Grafana-provided and expanded server-side before the SQL is sent:

$__timeFilter(column) expands to a bounded predicate using the dashboard's from/to. Omit it and the query ignores the time picker: zooming does nothing, the panel shows all history, and every load scans far more than it should. This is simultaneously a correctness bug and a performance bug.
$__timeGroup(column, $__interval) (and its alias-emitting variant) buckets rows to the dashboard's computed step, so zooming out aggregates instead of returning millions of rows. It usually accepts a fill option to decide whether gaps become nulls, zeros or the previous value — which is what stops a graph from drawing a straight line across an outage.
Epoch variants exist for integer timestamp columns, and $__from/$__to give the raw bounds for hand-written predicates.

Long versus wide. A relational result is naturally long: one row per (time, label, value). Many panels prefer wide: one time column and one numeric column per series. Grafana can convert — the time-series format mode does it when a string column is present, and a Prepare-time-series transformation does it explicitly — but knowing which form you have explains a large share of "the panel drew one series instead of five" confusion.

The diagnosis path

Open the panel inspector. The Data tab shows the field names and their types; the Query tab shows the SQL that Grafana actually sent, with macros expanded. Between them you can see in seconds whether the time column arrived as a string, whether the time filter was substituted, and whether the frame is long or wide. Guessing at panel options without looking at the frame types is the slow way to solve this.

Summary sentence for an interview

"Everything becomes data frames; the difference is who types them. Metrics and trace stores type themselves, document stores are typed by data-source configuration, and relational sources are typed by your query — a real time column, the right format mode, and the time-filter and time-group macros so the dashboard's range and resolution actually reach the database."

## One target shape: data frames Whatever the backing system, a Grafana data source returns **data frames**: named fields with declared types (time, number, string, boolean) plus labels and display metadata. A time series panel needs a field of type *time* and at least one *number* field. "Data is missing a time field" means exactly that — no field arrived with the time type — and everything else follows from who is responsible for supplying it. ## Metrics and trace stores: typed by the model A metrics store has a fixed data model: a series is identified by dimensions and carries (timestamp, value) pairs. The plugin therefore knows, without being told, which part of the response is time, which is the value, and which are labels. It emits well-typed frames automatically, which is why these sources "just work" in a time series panel and why the panel can label series from dimensions without configuration. Log and trace stores are similar: the response shape is known to the plugin, which is what lets Grafana attach specialised behaviour such as turning a field into a link to a trace. ## Document stores: typed by data-source configuration A schema-flexible document store has no single model, so the *data source configuration* carries the missing information: which field holds the timestamp, and which index or index pattern to read. The query editor then builds bucketed aggregations — a date histogram over that configured time field, with sub-aggregations for the metric and for grouping terms — so the frames are again well-typed without the query author naming the time column each time. The corollary is that misconfiguring the time field at data-source level breaks every query built on it, and changing it is a data-source-wide change. ## Relational sources: typed by you A relational database can return any shape at all, so Grafana requires explicit cooperation. **A time-typed column.** The column must arrive as a timestamp type. A `varchar` holding an ISO string, or a bigint of epoch seconds, is not a time field until it is converted — either cast in SQL, wrapped in the relevant epoch macro, or converted afterwards with a Convert-field-type transformation. This is the most common cause of the error in the question: the table view is happy to display strings, so everything looked fine there. **The Format-as choice.** The query editor offers *Time series*, *Table* and, for some engines, *Logs*. Table passes columns through as-is. Time series asks Grafana to interpret the result as series: a time column, an optional string column naming the metric/series, and one or more numeric value columns, ordered by time ascending. Returning columns in an unexpected order, or leaving the result unsorted, produces either an error or a graph that zig-zags backwards. **The macros.** These are Grafana-provided and expanded server-side before the SQL is sent: - `$__timeFilter(column)` expands to a bounded predicate using the dashboard's from/to. Omit it and the query ignores the time picker: zooming does nothing, the panel shows all history, and every load scans far more than it should. This is simultaneously a correctness bug and a performance bug. - `$__timeGroup(column, $__interval)` (and its alias-emitting variant) buckets rows to the dashboard's computed step, so zooming out aggregates instead of returning millions of rows. It usually accepts a fill option to decide whether gaps become nulls, zeros or the previous value — which is what stops a graph from drawing a straight line across an outage. - Epoch variants exist for integer timestamp columns, and `$__from`/`$__to` give the raw bounds for hand-written predicates. **Long versus wide.** A relational result is naturally *long*: one row per (time, label, value). Many panels prefer *wide*: one time column and one numeric column per series. Grafana can convert — the time-series format mode does it when a string column is present, and a Prepare-time-series transformation does it explicitly — but knowing which form you have explains a large share of "the panel drew one series instead of five" confusion. ## The diagnosis path Open the panel inspector. The Data tab shows the field names *and their types*; the Query tab shows the SQL that Grafana actually sent, with macros expanded. Between them you can see in seconds whether the time column arrived as a string, whether the time filter was substituted, and whether the frame is long or wide. Guessing at panel options without looking at the frame types is the slow way to solve this. ## Summary sentence for an interview "Everything becomes data frames; the difference is who types them. Metrics and trace stores type themselves, document stores are typed by data-source configuration, and relational sources are typed by your query — a real time column, the right format mode, and the time-filter and time-group macros so the dashboard's range and resolution actually reach the database."

Someone writes a relational panel query without the time-filter macro and hard-codes the last seven days. What is wrong with that?
The panel stops responding to the dashboard time picker: zooming in, zooming out and sharing a pinned range all show the same seven days, which quietly makes the dashboard lie during an investigation. It is also unbounded relative to the view — the database scans seven days even when the user is looking at ten minutes. The macro exists precisely so the selected range reaches the WHERE clause.
Your relational query returns 500,000 rows for a 30-day view and the browser struggles. What is the fix?
Bucket in the database instead of returning raw rows: group by the time-group macro at the dashboard's computed interval so the number of points tracks the panel's width rather than the row count. Set Max data points and a Min interval so the interval is sensible, and aggregate the value in SQL. Reducing the rows in a browser transformation does not help, because they have already been fetched.

saying these in an interview costs you the question

Assuming any column that looks like a date is a time field
Omitting the time-filter macro and hard-coding a range, so the time picker is ignored
Selecting Format as Table and then wondering why the time series panel refuses the data
Returning raw rows for wide ranges instead of bucketing in the database
Believing the panel inspector shows only values, when it also shows field types — the fastest way to diagnose this

A relational query renders fine in Grafana's table view, but the Time series panel reports that the data is missing a time field. Explain what Grafana needs from a SQL data source to draw a time series, and how that differs from what a metrics store or a document store returns.

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

One target shape: data frames

Metrics and trace stores: typed by the model

Document stores: typed by data-source configuration

Relational sources: typed by you

The diagnosis path

Summary sentence for an interview

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions3 questions

▾related topics3 topics

One target shape: data frames

Metrics and trace stores: typed by the model

Document stores: typed by data-source configuration

Relational sources: typed by you

The diagnosis path

Summary sentence for an interview

answer

go deeper

context