Building a Time-Series Panel

Introduction: From Connection to Visualization

In the previous lesson, you successfully connected Grafana to your PostgreSQL database and verified that the connection was working. That was an important first step, but now comes the exciting part — actually using that connection to create visualizations. In this lesson, you'll move from simply being connected to actively querying your data and seeing it displayed on a chart. You might be thinking, "I know SQL, so writing queries in Grafana should be straightforward, right?" Well, almost. Grafana does use SQL, but it adds some special functionality on top of it. When you write a query for a Grafana panel, you're not just retrieving data — you're retrieving data in a specific format that Grafana can transform into beautiful, interactive charts. Think of it as SQL with superpowers that connect directly to Grafana's time range controls and visualization engine. By the end of this lesson, you'll have written your first time-series query that displays CPU usage across different hosts. You'll understand the specific structure Grafana needs and learn to use special macros that make your queries work seamlessly with Grafana's interface. This pattern you learn here will become the foundation for every time-series panel you create going forward.

Getting to the Query Editor (Explore)

Note: From this unit forward, anonymous mode is enabled in all practice exercises so you won't need to sign in each time. This is for lab convenience only—never use anonymous access in production. In real environments, always require proper authentication and restrict permissions according to best practices. For more information, see the Grafana authentication documentation . Before you can write any queries, you need to know where to write them. Grafana provides a dedicated space called the Explore section specifically for testing and developing queries before adding them to dashboards. You can find Explore in the left sidebar — it looks like a compass icon. When you click on it, Grafana opens a query workspace where you can experiment with data retrieval and visualization. Once you're in Explore, notice that your configured Postgres data source — in this lab environment, it's named Postgres Local — is already selected at the top. When you open Explore with this data source, Grafana helpfully displays a list of available tables in your database. You can see tables like log_events, metrics_cpu, metrics_disk, and others that contain your demo metrics data. This gives you a quick reference of what data is available to query. What you're looking at right now is the Builder tab — Grafana's visual, form-based interface for constructing queries. You can select tables and columns from dropdowns and build queries without writing SQL directly. While the Builder can be useful for simple queries, we're going to work with the Code tab instead, which gives you much more freedom and control.

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

Enter the Magic Macros

ts	host	usage
2025-11-13 18:48:04	host-a	28.0
2025-11-13 18:48:04	host-b	24.9
2025-11-13 18:49:04	host-a	30.3
2025-11-13 18:49:04	host-b	23.3
2025-11-13 18:50:04	host-a	32.1
2025-11-13 18:50:04	host-b	26.7
...	...	...

Now, here's where things get interesting. Instead of writing long, complicated SQL date formatting functions, Grafana gives you special shortcuts called macros. They're like having a helpful assistant who writes the boring SQL for you.

The first macro you'll love is $__time(ts). When you use this in your query, you're basically saying: "Hey Grafana, take this timestamp column called ts and format it exactly how you need it for visualization." Grafana handles all the technical details behind the scenes. No need to remember database-specific date functions — the macro does it for you.

The second macro is even cooler: $__timeFilter(ts). This one is pure magic. You know that time range picker at the top of Grafana dashboards where you can select "Last 15 minutes" or "Last 7 days"? Well, $__timeFilter(ts) automatically connects your query to that picker.

Let's say someone selects "Last 24 hours" in the dashboard. Your query instantly filters to show only the last 24 hours of data. They change it to "Last week"? Your query automatically adjusts. You don't have to write different queries for different time ranges — the macro handles everything. It's like having a query that reads the user's mind!

These macros are what transform your basic SQL into a dynamic, interactive Grafana experience. Once you get comfortable with them, you'll wonder how you ever lived without them.

Here's an important detail: technically, the time picker will still work even if you don't use $__timeFilter() — Grafana will filter the data on its end. But here's the catch: without the macro, your database loads every single row from the table and sends all that data to Grafana, which then throws away everything outside your selected time range. With small demo datasets, you won't notice. But in production with millions of rows? Your query will crawl to a halt. The $__timeFilter() macro is what tells PostgreSQL "only send data from the selected time period," making your database do the heavy lifting efficiently with indexes. Always use it — it's the difference between a query that takes 10 seconds versus one that takes 10 milliseconds.

Building Your CPU Query Step-by-Step

Let's now put all the pieces together and build your first Grafana time-series query. You understand your source data (metrics_cpu with its ts, host, and usage columns), you know what Grafana expects (columns named time, metric, and value), and you've learned about the magical macros. Time to combine them!

Start with the SELECT clause. You need to transform your three source columns into Grafana's expected format. For the time column, use $__time(ts) — this macro automatically formats your timestamp. For the value column, select usage AS value to rename your CPU percentage. For the metric column, select host AS metric to identify which server each measurement belongs to.

The FROM clause is straightforward: FROM public.metrics_cpu tells the database where to get the data.

Your WHERE clause is where the second macro shines. Add WHERE $__timeFilter(ts) to connect your query to Grafana's time range picker. This single line makes your query dynamically responsive to user selections.

Finally, add ORDER BY 1 to sort results by the first column (time) in chronological order. This ensures your visualization displays data points from oldest to newest.

Here's the complete query:

SQL

-- Basic CPU usage time-series by host
SELECT
  $__time(ts),
  usage AS value,
  host  AS metric
FROM public.metrics_cpu
WHERE $__timeFilter(ts)
ORDER BY 1;

-- Basic CPU usage time-series by host
SELECT
  $__time(ts),
  usage AS value,
  host  AS metric
FROM public.metrics_cpu
WHERE $__timeFilter(ts)
ORDER BY 1;

Before running this query, notice the Format dropdown near the top of the query editor. This dropdown tells Grafana how to interpret your query results. The two most important options are Time series (which creates line charts for time-based data) and Table (which displays data in rows and columns). For visualizing CPU usage trends, select Time series — this is what transforms your three columns into that beautiful multi-line graph.

When you run the query with Time series format selected, you'll see exactly what's shown in the image above. Each host gets its own colored line tracking CPU usage over time. Hover over any point to see exact values, or use your mouse to zoom into specific time ranges. This is the power of combining proper SQL aliasing, Grafana macros, and the right format selection.

Introduction: From Connection to Visualization

Getting to the Query Editor (Explore)

Join the 1M+ learners on CodeSignal

Switching to the Code Tab (and Verifying Tables)

Understanding Your Data: The metrics_cpu Table

What Grafana Expects from Time-Series Data

Enter the Magic Macros

Building Your CPU Query Step-by-Step

Summary and What's Next