# PostgreSQL: Trivial timeseries examples

08.2019 / Category: / Tags:

Timeseries are an increasingly important topic - not just in PostgreSQL. Recently I gave a presentation @AGIT in Salzburg about timeseries and I demonstrated some super simple examples. The presentation was well received, so I decided to share this stuff in the form of a blog, so that more people can learn about window functions and SQL in general. A link to the video is available at the end of the post so that you can listen to the original material in German.

To show how data can be loaded, I compiled a basic dataset which can be found on my website. Here is how it works:

The cool thing is that if you happen to be a superuser, you can easily load the data from the web directly. COPY FROM PROGRAM allows you to execute code on the server and pipe it directly to PostgreSQL, which is super simple. Keep in mind: that only works if you are a PostgreSQL superuser (for security reasons).

## lag: The backbone of timeseries analysis

If you are dealing with timeseries, calculating the difference to the previous period is really important. Fortunately, SQL allows you to do that pretty easily. Here is how it works:

The lag functions takes two parameters: The first column defines the column, which should be used in this case. The second parameter is optional. If you skip it, the expression will be equivalent to lag(production, 1). In my example, the lag column will be off by one. However, you can use any integer number to move data up or down, given the order defined in the OVER clause.

What we have so far is the value of the previous period. Let us calculate the difference next:

That was easy. All we have to do is to take the current row and subtract the previous row.
Window functions are far more powerful than shown here, but maybe this example will help to get you started in the first place.

## Calculating correlations using PostgreSQL

You may want to calculate the correlation between columns. PostgreSQL offers the “corr” function to do exactly that. The following listing shows a simple example:

As you can see, the correlation in Mexico and Canada are highest.

## Checking for continuous activity

In the past we presented other examples related to timeseries and analysis in general. One of the most interesting posts is found here.

If you want to see the entire short presentation in German consider checking out the following video.

Article Rating
Subscribe
Notify of
Inline Feedbacks
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Support Platform
Get the newest PostgreSQL Info & Tools

2024
CYBERTEC PostgreSQL International GmbH

You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.

You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.

You are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.