How to visualize timeshifts to compare metrics over time in Grafana using PostgreSQL

Learn how (and why) to combine PostgreSQL, TimescaleDB, and Grafana to visualize timeshifts and compare how your metrics change over time.

The problem: Comparing metrics over time (aka timeshifting)

When we’re doing real-time monitoring or historical analysis, we often want to visually compare the value of a metric NOW to the value X days, weeks, hours, or months ago (or, in other words, we want to compare its value at the current time to its value timeshifted one or more intervals of time ago).

This is known as a timeshift: comparing a metric against itself, but for a different time period.

This is especially common in DevOps, IoT, and user behavior analysis scenarios, where we want to understand if things like upticks or downticks are seasonal, or a result of something new – as well as a host of other questions that require us to analyze how certain metrics change over time.

For example, take the case of monitoring taxi rides. On any given day, we might ask things like: how does the ride activity today compare with activity over the last 3 days? Or, how does ride activity this Friday compare to Friday last week? What about the week before? Or the same time last year? These questions about taxi rides could easily apply to our website uptime metrics, our CPU utilization, and so forth...

One (painful) way to answer these questions might be to create separate graphs for each time interval and manually compare them by eye. However, this isn’t very efficient, and manual comparison can be mentally taxing.

Two separate graphs showing rides for consecutive weeks in January 2016 — Graphs comparing taxi rides taken in week 1 and week 2 of January 2016. Notice how difficult it is to compare ride activity between the two graphs.

The solution: Use PostgreSQL LATERAL JOIN

A better way would be to have all trend lines (both for current activity and timeshifted activity) on a single graph. However, in Grafana, this isn't always possible, depending on which datasource you use. For example, Grafana’s Graphite datasource supports timeshift natively, but many others do not.

For the PostgreSQL datasource, timeshifting is possible, and the best way to create time-shifted graphs is to use PostgreSQL’s LATERAL JOIN function.

Using the LATERAL JOIN function, we can create timeshifted graphs for monitoring and historical analysis like these:

Graph showing rides for a given time period, with timeshifts for the previous 3 days — Timeshifted graph showing taxi rides for today (green) and last 3 days.

Graph showing rides for a given time period, with timeshift for the previous week — Timeshifted graph showing taxi rides for given day (yellow line) and previous week (green line)

Try It Yourself: Implementation in Grafana & Sample Queries

To help you get the hang of creating timeshifted graphs on a sample dataset before applying it to your own projects, I’ve put together this handy step-by-step guide.

Scenario

We’ll use the use case of monitoring IoT devices, specifically taxis equipped with location-detecting sensors. Our dataset comes from the New York Taxi and Limousine Commission (NYC TLC) for the month of January 2016.

Prerequisites

TimescaleDB instance (Timescale Cloud or self-hosted), running PostgreSQL 11+
Grafana instance (cloud or self-hosted)
TimescaleDB instance connected to Grafana (see our Grafana setup tutorial)To load the taxi dataset into TimescaleDB, complete Mission 1 in this tutorial, which will take you through downloading the .CSV file and inserting the data into your database.

Example 1: Building a 3 Day Timeshift

Let’s say we wanted to answer: “how does taxi ride activity today compare with the activity from the previous 3 days?”

Here’s the full query, with annotations, showing how to use the PostgreSQL LATERAL JOIN function to create a graph that displays the current number of rides, as well as timeshifted rides from the previous 3 days.

-- What to name the series
SELECT time, ride_count, CASE WHEN step = 0 THEN 'today' ELSE (-interval)::text END AS metric
FROM
-- sub-query to generate the intervals
    ( SELECT step, (step||'day')::interval AS interval FROM generate_series(0,3) g(step)) g_offsets
    JOIN LATERAL (
-- subquery to select the rides 
    SELECT
-- adding set interval to time values
      time_bucket('15m',pickup_datetime + interval)::timestamptz AS time, count(*) AS ride_count FROM rides
-- subtract value of interval from time to plot
-- today = 0, 1 day ago = 1, etc
    WHERE pickup_datetime BETWEEN $__timeFrom()::timestamptz - interval AND $__timeTo()::timestamptz - interval
    GROUP BY 1
    ORDER BY 1
    ) l ON true

Query to plot rides in 15 minute intervals, with timeshifts for the previous 3 days

This produces the following graph:

Graph showing taxi rides in January 2016 along with timeshifts for the previous 3 days — Graph showing taxi rides taken in January 2016, timeshifted to compare rides today to prior three days. Today’s rides shown in green, -1 day in red, -2 days in blue, -3 days in yellow.

If we zoom into a 2 day time period (by selecting it using the timepicker or highlighting it in the graph), we can see how timeshifting allows us to compare ride activity, simply by hovering over the graph at any given time interval:

Timeshifted graph for 2 week period in January 2016 — Graph showing taxi rides taken in January 12 and January 13 2016, timeshifted to compare rides today to the prior three days, zoomed in to an arbitrary 2 day period. Today’s rides shown in green, -1 day in red, -2 days in blue, -3 days in yellow.

How the query works:

In this query, the LATERAL JOIN functions like a “for each” loop, making the results of the sub-query before the LATERAL JOIN available to each result of the sub-query which comes after it.

In this case, the query before the LATERAL JOIN generates the intervals we want to compare ride activity over. We generate the intervals of 0,1,2 and 3 days, since we want to compare ride behaviour on any given day, to that of the previous 3 days:

-- sub-query to generate the intervals
( SELECT step, (step||'day')::interval AS interval 
FROM generate_series(0,3) g(step)) g_offsets

Query to generate intervals to compare ride activity over

In the query after the LATERAL JOIN, we plot the number of rides in our time period of interest in 15 minute time buckets. Notice how we use our interval value from the previous sub-query: by adding and subtracting the interval in the time_bucket function and in the WHERE clause to filter the time-range for the rides selected, we’re able to get the correct values for current and timeshifted intervals:

-- subquery to select the rides 
(SELECT
-- adding set interval to time values  time_bucket('15m',pickup_datetime + interval)::timestamptz AS time, count(*) AS ride_count 
FROM rides
-- subtract value of interval from time to plot
-- today = 0, 1 day ago = 1, etc
WHERE pickup_datetime BETWEEN $__timeFrom()::timestamptz - interval 
AND $__timeTo()::timestamptz - interval
GROUP BY 1
ORDER BY 1)

Query to we plot the number of rides in 15 minute time buckets for current and timeshifted periods

For more on LATERAL JOIN, see this useful tutorial from the folks at Heap and the official PostgreSQL docs.

Visual Pro-tip: Add a series override

In order to make it easier to distinguish between rides for any given day and rides from the previous 3 days, we can apply a series override in order modify the appearance of the timeshifted lines:

Series override parameters to distinguish between real and timeshifted lines

Using the parameters above, we apply a series override to the timeshifted series in order to give them a smaller line width than the real line, allowing us to distinguish between them more easily. We can then look of the non-timeshifted line under the Display settings -- in the image below line width is set to 5 and line are to 2:

Graph showing different line styles for current rides and time shifted rides — Final graph for current rides and previous 3 day timeshift with visual treatment applied

Example 2: Building a 1 Week Timeshifts

Next, we want to answer: “How does the activity this week compare to last week?”

In this example, we create a graph to display the current number of rides, as well as a timeshifted line to graph the rides from the previous week.

Much of the query is the same as in Example 1; the only differences are (1) the interval definition changes from day to week and (2) the series we generate only has two values, 0 and 1, since we only want to compare to the previous week (vs. the 3 day period in the prior example.)

SELECT time, ride_count, 
	CASE WHEN step = 0 THEN 'today' ELSE (-interval)::text END AS metric
FROM
    ( SELECT step, (step||'week')::interval AS interval FROM generate_series(0,1) g(step)) g_offsets
JOIN LATERAL (
    SELECT
      time_bucket('15m',pickup_datetime + interval)::timestamptz AS time, count(*) AS ride_count FROM rides
    WHERE
      pickup_datetime BETWEEN $__timeFrom()::timestamptz - interval AND $__timeTo()::timestamptz - interval
    GROUP BY 1
    ORDER BY 1
) l ON true

Query to plot current rides and 1 week timeshifted rides

This produces the following graph:

Graph showing taxi rides taken in January 2016, time-shifted to compare rides today to the previous week, zoomed in to an arbitrary 5 day period. Rides for a given day are shown in green and rides on that day last from the previous week are shown in yellow

Visual Pro-tip: Add a series override

To make it easier to distinguish between rides for any given day and rides from the previous week, we can apply a series override that modifies the appearance of the timeshifted lines:

Graph showing current rides and 1 week timeshifted rides with series override applied to visually distinguish betweeen timeshift and non-timeshifted lines

To achieve this, we set the line area to 0 (under Display settings), and then apply a series override to the timeshifted series.

In the series override settings, we:

Set the line fill to 2, giving us a shadow look
Set the line width to 0, leaving the non-timeshifted graph as the only series with a solid line, making it more distinguishable.

Series override paramters to distinguish between timeshifted lines and non-timeshifted lines — Series override settings for making time-shifted lines more distinguishable

Next Steps

In this tutorial, we covered what timeshifting is, how it works, and how to use PostgreSQL LATERAL JOIN, TimescaleDB, and Grafana to visualize timeshifts to easily compare data across two (or more!) time periods.

⏰ To modify the query to timeshift any arbitrary number of minutes, hours, days, months, or years, change the parameters on generate_series and interval definition (while it’s most common to compare metrics NOW to previous periods, you can use time-shifting to compare ANY two time periods).

Happy timeshifting!

Learn More

Want more Grafana tips? Explore our Grafana tutorials (I recommend this one on variables and this one on visualizing missing data).

Need a database to power your dashboarding and data analysis? Get started with Timescale Cloud (it’s our fast, easy-to-use, and reliable time-series database built on PostgreSQL, available in 75+ cloud regions). When you sign up, you’ll see $300 in credits to get you up and running.

This post was written by