This tutorial uses a dataset that contains second-by-second stock-trade data for
the top 100 most-traded symbols, in a hypertable named
also includes a separate table of company symbols and company names, in a
regular PostgreSQL table named
Install Timescale Cloud by signing up for an account. It's free for thirty days.
- Sign up for a Timescale Cloud account with your name and email address. You do not need to provide payment details to get started. A confirmation email is sent to the email address you provide.
- Verify your email by clicking on the link in the email you received. Don't forget to check your spam folder in case the email ends up there.
- Sign in to the Timescale Cloud portal with the
password you set:
Your Timescale Cloud trial is completely free for you to use for the first thirty days. This gives you enough time to complete all our tutorials and run a few test projects of your own.
When you have a service up and running, you can connect to it 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
Sign in to the Timescale Cloud portal.
Servicestab, find the service you want to connect to, and check it is marked as
Click the name of the service you want to connect to see the connection information. Take a note of the
Navigate to the
Operationstab, and click
Reset password. You can choose your own password for the service, or allow Timescale Cloud to generate a secure password for you. Take a note of your new password.
On your local system, at the command prompt, connect to the service using the service URL. When you are prompted for the password, enter the password you just created:psql -x "<SERVICE_URL>"Password for user tsdbadmin:
If your connection is successful, you'll see a message like this, followed by the
psqlprompt: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=>
The dataset is updated on a nightly basis and contains data from the last four weeks, typically around 8 million rows of data. Stock trades are recorded in real-time Monday through Friday, typically during normal trading hours of the New York Stock Exchange (9:30 AM - 4:00 PM EST).
Hypertables are the core of TimescaleDB. Hypertables enable TimescaleDB to work efficiently with time-series data. Because TimescaleDB is PostgreSQL, all the standard PostgreSQL tables, indexes, stored procedures, and other objects can be created alongside your TimescaleDB hypertables. This makes creating and working with TimescaleDB tables similar to standard PostgreSQL.
Create a standard PostgreSQL table to store the real-time cryptocurrency data using
CREATE TABLE:CREATE TABLE crypto_ticks ("time" TIMESTAMPTZ,symbol TEXT,price DOUBLE PRECISION,day_volume NUMERIC);
Convert the standard table into a hypertable partitioned on the
timecolumn using the
create_hypertable()function provided by TimescaleDB. 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('crypto_ticks', 'time');
When you have other relational data that enhances your time-series data, you can
create standard PostgreSQL tables just as you would normally. For this dataset,
there is one other table of data called
Add a table to store the company name and symbol for the stock trade data:CREATE TABLE crypto_assets (symbol TEXT UNIQUE,"name" TEXT);
You now have two tables within your TimescaleDB database. One hypertable named
crypto_ticks, and one normal PostgreSQL table named
This tutorial uses real-time cryptocurrency data, also known as tick data, from Twelve Data.
To ingest data into the tables that you created, you need to download the dataset and copy the data to your database.
crypto_sample.zipfile. The file contains two
.csvfiles; one with company information, and one with real-time stock trades for the past month. Download:
In a new terminal window, run this command to unzip the
psqlprompt, use the
COPYcommand to transfer data into your TimescaleDB instance. If the
.csvfiles aren't in your current directory, specify the file paths in these commands:\COPY crypto_ticks FROM 'tutorial_sample_tick.csv' CSV HEADER;\COPY crypto_assets FROM 'tutorial_sample_assets.csv' CSV HEADER;
Because there are millions of rows of data, the
COPYprocess could take a few minutes depending on your internet connection and local client resources.
Found an issue on this page?Report an issue!