Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 37 ยท Queries and Transformations

SQL and Loki Queries in Grafana: Macros, Time Series and Logs

Use Grafana's SQL macros such as $__timeFilter and $__timeGroup, shape results for time series or tables, and write Loki LogQL log and metric queries.

Prometheus is not the only source. Many teams chart business data straight from PostgreSQL or MySQL, and search logs in Loki. Each has its own query dialect, and Grafana adds helpers on top. This lesson covers the SQL macros, how to format SQL results so Grafana draws them correctly, and the basics of LogQL.

Grafana, PostgreSQL and Loki do not run on this site. The TryIt blocks use Python with its built-in SQLite and plain string handling as clearly labelled stand-ins, so you can see what the macros expand to and what shape the data must have.

SQL data sources

The PostgreSQL, MySQL and Microsoft SQL Server data sources share a query editor with a Builder (choose a table, columns, filters, group by) and a Code mode with a text box. The key setting is Format as: Time series or Table. The same query behaves differently depending on this choice.

For Time series, the result must have:

  • a column named time (or the first column), as a timestamp or Unix epoch, sorted ascending;
  • one or more numeric columns (each becomes a series);
  • optionally a text column called metric, which names the series when you have long format data.

If you return a table with rows (time, host, value) and use time series format, Grafana can turn it into many series when you alias the text column to metric. Otherwise Grafana expects "wide" data, one numeric column per series.

Macros

A macro is a placeholder that Grafana replaces with real SQL before the query runs, using the dashboard time range. Macros save you from writing timestamps by hand. The common ones are:

Macro Expands to
$__time(col) col AS "time"
$__timeFilter(col) A WHERE-ready condition: col BETWEEN from AND to
$__timeFrom() / $__timeTo() The start and end of the range
$__timeGroup(col, '1h') Truncates col to buckets of one hour (also $__timeGroupAlias, which adds AS "time")
$__unixEpochFilter(col) Like $__timeFilter for integer epoch seconds
$__interval The calculated step; often used with $__timeGroup(col, $__interval)

The exact set and spelling vary a little between data sources (PostgreSQL also has $__timeGroup with a fill option, MySQL uses $__timeGroup(col, '5m'), and so on), so check the editor's help panel for your database. A typical Postgres panel query looks like this:

SQL
SELECT
  $__timeGroupAlias(created_at, '1h'),
  region AS metric,
  SUM(total) AS revenue
FROM orders
WHERE $__timeFilter(created_at)
GROUP BY 1, 2
ORDER BY 1

Always group and order by the time column; otherwise points arrive out of order and the line zigzags.

Python Stand-in: expand macros, then run the SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The row at timestamp 99999 is dropped by the time filter, and the other rows are grouped into hourly buckets, one (time, metric, value) triple per series point. That is the long format Grafana can split into one series per metric.

Table format and other pitfalls

Choose Table when you want a plain result: top customers, a list of failed jobs, a single SELECT COUNT(*) for a Stat panel. Table format has no time column requirement. Common mistakes:

  • Forgetting $__timeFilter: the panel scans the whole table and ignores the picker.
  • Storing time as a string ('2025-01-05'): cast it to a real timestamp or the macro compares text.
  • Unsorted time: add ORDER BY time.
  • Putting variables in SQL as raw text: use $var (Grafana quotes multi-value variables with the sqlstring format automatically in most SQL sources) rather than concatenating; this also avoids injection from values typed in a text box variable.
  • Missing time zone care: the macros use UTC internally; your column should be stored in UTC or converted explicitly.

Loki and LogQL

Loki stores logs indexed only by labels. A LogQL query starts with a stream selector in braces, followed by optional filters and parsers:

LOGQL
{app="checkout", env="prod"} |= "timeout" != "healthcheck" | json | level="error"

Read it left to right: pick streams with those labels, keep lines containing timeout, drop lines containing healthcheck, parse each line as JSON so its fields become labels, then keep lines where level is error. Other line filters are |~ (regex match) and !~ (regex not match). Other parsers are logfmt, pattern, regexp and unpack.

LogQL can also turn logs into metrics, which draw as normal time series:

LOGQL
sum by (status) (rate({app="checkout"} | json [5m]))
count_over_time({app="checkout"} |= "error" [1h])

In a Logs panel or in Explore, a plain log query shows lines with a level color, a log volume histogram, and a detail view where you can click a label to filter. In a Time series panel, a metric query returns numbers. The same $__auto and $__interval ideas apply, written like [$__auto] for the range.

Python Stand-in: filter log lines like |= and !=

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Only payment timeout survives: the healthcheck line is dropped by !=, and the warn line fails the level filter.

Choosing between SQL and Loki

Use SQL when the facts already live in a relational database and you want business trends or tables. Use Loki when you need to search text and count events across many hosts without indexing the whole line. Both can be combined on one dashboard, and a Mixed data source panel (see expressions and mixed queries) can plot database numbers next to log-derived rates.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the $__timeFilter(col) macro do?

    The macro expands to a time range condition that you place in the WHERE clause.
  2. 2. A SQL time series panel zigzags back and forth. What is the likeliest cause?

    Grafana draws points in the order returned, so add ORDER BY on the time column.
  3. 3. In LogQL, what does {app="checkout"} |= "timeout" do?

    Braces select streams; |= is a line filter that keeps lines containing the string.
  4. 4. Which format should a Stat panel showing SELECT COUNT(*) use?

    A single value has no time series structure, so table format is the right fit.

Summary

  • SQL data sources format results as time series (sorted time column plus numbers) or as a table.
  • Macros like $__timeFilter, $__timeGroup and $__interval bind queries to the dashboard time range.
  • Alias a text column to metric to split long data into several series.
  • LogQL = stream selector + line filters + parsers + optional metric functions such as rate and count_over_time.
  • Pick SQL for relational facts and Loki for text-heavy event data; combine them with Mixed panels.

Next: Transformations