Set up the database

This tutorial uses the energy consumption data for over a year in a hypertable named metrics.

A service in Timescale is a cloud instance which contains your database. Each service contains a single database, named tsdb. You can connect to a service from your local system using the psql command-line utility. If you've used PostgreSQL before, you might already have psql installed. If not, check out the installing psql section.

Create a Timescale service and connect to the service

In the Timescale portal, click Create service.
Click Download the cheatsheet to download an SQL file that contains the login details for your new service. You can also copy the details directly from this page. When you have copied your password, click I stored my password, go to service overview at the bottom of the page.
When your service is ready to use, is shows a green Running label in the Service Overview. You also receive an email confirming that your service is ready to use.
On your local system, at the command prompt, connect to the service using the Service URL from the SQL file that you downloaded. When you are prompted, enter the password:
```
psql -x "<SERVICE_URL>"
Password for user tsdbadmin:
```
If your connection is successful, you'll see a message like this, followed by the psql prompt:
```
psql (13.3, server 12.8 (Ubuntu 12.8-1.pgdg21.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
tsdb=>
```

This tutorial uses the energy consumption data for over a year in a typical household. You can use this data to analyze the energy consumption pattern.

Create a hypertable

Hypertables are the core of Timescale. Hypertables enable Timescale to work efficiently with time-series data. Because Timescale is PostgreSQL, all the standard PostgreSQL tables, indexes, stored procedures and other objects can be created alongside your Timescale hypertables. This makes creating and working with Timescale tables similar to standard PostgreSQL.

Creating a hypertable

Create a standard PostgreSQL table to store the energy consumption data using CREATE TABLE:


CREATE TABLE "metrics"(
    created timestamp with time zone default now() not null,
    type_id integer                                not null,
    value   double precision                       not null
);

Convert the standard table into a hypertable partitioned on the time column using the create_hypertable() function provided by Timescale. You must provide the name of the table and the column in that table that holds the timestamp data to use for partitioning:
```
SELECT create_hypertable('metrics', by_range('created'));
```
Note
The by_range dimension builder is an addition to TimescaleDB 2.13.

Load energy consumption data

When you have your database set up, you can load the energy consumption data into the metrics hypertable.

Loading energy consumption data

Important

This is a large dataset, so it might take a long time, depending on your network connection.

Download the dataset:
metrics.csv.gz
Use your file manager to decompress the downloaded dataset, and take a note of the path to the metrics.csv file.
At the psql prompt, copy the data from the metrics.csv file into your hypertable. Make sure you point to the correct path, if it is not in your current working directory:
```
\COPY metrics FROM metrics.csv CSV;
```

You can check that the data has been copied successfully with this command:


SELECT * FROM metrics LIMIT 5;

You should get five records that look like this:


created            | type_id | value 
-------------------------------+---------+-------
 2023-05-31 23:59:59.043264+00 |      13 |  1.78
 2023-05-31 23:59:59.042673+00 |       2 |   126
 2023-05-31 23:59:59.042667+00 |      11 |  1.79
 2023-05-31 23:59:59.042623+00 |      23 | 0.408
 2023-05-31 23:59:59.042603+00 |      12 |  0.96

Time-series data usually grows very quickly. And that means that aggregating the data into useful summaries can become very slow. Continuous aggregates makes aggregating data lightning fast.

If you are collecting data very frequently, you might want to aggregate your data into minutes or hours instead. For example, if you have a table of temperature readings taken every second, you can find the average temperature for each hour. Every time you run this query, the database needs to scan the entire table and recalculate the average every time.

Continuous aggregates are a kind of hypertable that is refreshed automatically in the background as new data is added, or old data is modified. Changes to your dataset are tracked, and the hypertable behind the continuous aggregate is automatically updated in the background.

You don't need to manually refresh your continuous aggregates, they are continuously and incrementally updated in the background. Continuous aggregates also have a much lower maintenance burden than regular PostgreSQL materialized views, because the whole view is not created from scratch on each refresh. This means that you can get on with working your data instead of maintaining your database.

Because continuous aggregates are based on hypertables, you can query them in exactly the same way as your other tables, and enable compression or tiered storage on your continuous aggregates. You can even create continuous aggregates on top of your continuous aggregates.

By default, querying continuous aggregates provides you with real-time data. Pre-aggregated data from the materialized view is combined with recent data that hasn't been aggregated yet. This gives you up-to-date results on every query.

Create continuous aggregates

Creating continuous aggregates for energy consumption by day and hour

Create a continuous aggregate kwh_day_by_day for energy consumption on a day to day basis:


CREATE MATERIALIZED VIEW kwh_day_by_day(time, value)
    with (timescaledb.continuous) as
SELECT time_bucket('1 day', created, 'Europe/Berlin') AS "time",
        round((last(value, created) - first(value, created)) * 100.) / 100. AS value
FROM metrics
WHERE type_id = 5
GROUP BY 1;

Add a refresh policy to keep the continuous aggregate up-to-date:


SELECT add_continuous_aggregate_policy('kwh_day_by_day',
   start_offset => NULL,
   end_offset => INTERVAL '1 hour',
   schedule_interval => INTERVAL '1 hour');

Create a continuous aggregate kwh_hour_by_hour for energy consumption on an hourly basis:


CREATE MATERIALIZED VIEW kwh_hour_by_hour(time, value)
  with (timescaledb.continuous) as
SELECT time_bucket('01:00:00', metrics.created, 'Europe/Berlin') AS "time",
       round((last(value, created) - first(value, created)) * 100.) / 100. AS value
FROM metrics
WHERE type_id = 5
GROUP BY 1;

Add a refresh policy to keep the continuous aggregate up-to-date:


SELECT add_continuous_aggregate_policy('kwh_hour_by_hour',
   start_offset => NULL,
   end_offset => INTERVAL '1 hour',
   schedule_interval => INTERVAL '1 hour');

You can confirm that the continuous aggregates were created:


SELECT view_name, format('%I.%I', materialization_hypertable_schema,materialization_hypertable_name) AS materialization_hypertable
FROM timescaledb_information.continuous_aggregates;

You should see this:


view_name     |            materialization_hypertable
------------------+--------------------------------------------------
 kwh_day_by_day   | _timescaledb_internal._materialized_hypertable_2
 kwh_hour_by_hour | _timescaledb_internal._materialized_hypertable_3

The queries in this tutorial are suitable for visualizing in Grafana. If you want to visualize the results of your queries, connect your Grafana account to the energy consumption dataset.

Add Timescale as a data source in Grafana

Grafana is and open source analytics and monitoring solution. You use Grafana to visualize queries directly from your Timescale Cloud service.

Prerequisites

Before you import your data:

Create a target Timescale Cloud service.
Each Timescale Cloud service has a single database that supports the most popular extensions. Timescale Cloud services do not support tablespaces, and there is no superuser associated with a Timescale service.

Install self-managed Grafana, or sign up for Grafana Cloud

Add your Timescale Cloud service as a data source in Grafana

To connect the data in your Timescale Cloud service to Grafana:

Log in to Grafana
In your browser, log in to either :
- Self-hosted Grafana: at http://localhost:3000/. The default credentials are admin, admin.
- Grafana Cloud: use the URL and credentials you set when you created your account.
Add your Timescale Cloud service as a data source
1. In the Grafana dashboard, navigate to Configuration > Data sources, then click Add data source.
2. In Add data source, select PostgreSQL.
3. Configure the data source using the connection in $TARGET:
  - Name: the name to use for the dataset
  - Host: the host and port for your service, in this format: <HOST>:<PORT>.
    For example: example.tsdb.cloud.timescale.com:35177.
  - Database: tsdb
  - User: tsdbadmin, or another privileged user
  - Password: the password for User
  - TLS/SSL Mode: select require
  - PostgreSQL details: enable TimescaleDB
  - Leave the default setting for all other fields
4. Click Save & test.
Grafana checks that your details are set correctly.

Set up the database

Create a Timescale service and connect to your service

Create a Timescale service and connect to the service

The dataset

Create a hypertable

Creating a hypertable

Note

Load energy consumption data

Loading energy consumption data

Important

Downsampling the data

Create continuous aggregates

Creating continuous aggregates for energy consumption by day and hour

Connect to Grafana

Add Timescale as a data source in Grafana

Prerequisites

Add your Timescale Cloud service as a data source in Grafana