Add time-series data

To explore Timescale's features, you need some sample data. This tutorial provides real-time stock trade data, also known as tick data, from Twelve Data.

Note

Alternatively, if you have data in an existing database, you can migrate it directly into your new Timescale database using hypershift. For more information about hypershift, including instructions for how to migrate your data, see the hypershift documentation.

The dataset contains second-by-second stock-trade data for the top 100 most-traded symbols, in a hypertable named stocks_real_time. It also includes a separate table of company symbols and company names, in a regular PostgreSQL table named company.

The dataset is updated on a nightly basis and contains data from the last four weeks, typically ~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).

Note

In case you want to ingest real-time data, instead of sample data, read the tutorial Ingest real-time financial websocket data and ingest data directly from the Twelve Data financial API.

stocks_real_time: contains stock data. Includes stock price quotes at every second during trading hours.

company: contains a mapping for symbols to company names.

To ingest data into the tables that you created, you need to download the dataset and copy the data to your database.

Note

Alternatively, if you have data in an existing database, you can migrate it directly into your new Timescale database using hypershift. For more information about hypershift, including instructions for how to migrate your data, see the hypershift documentation.

Ingesting the dataset

1. Download the real_time_stock_data.zip file. The file contains two .csv files; one with company information, and one with real-time stock trades for the past month. Download:

   real_time_stock_data.zip

2. In a new terminal window, run this command to unzip the .csv files:

   unzip real_time_stock_data.zip

   Copy

3. At the psql prompt, use the COPY command to transfer data into your Timescale instance. If the .csv files aren't in your current directory, specify the file paths in the following commands:

   \COPY stocks_real_time from './tutorial_sample_tick.csv' DELIMITER ',' CSV HEADER;

   Copy

   \COPY company from './tutorial_sample_company.csv' DELIMITER ',' CSV HEADER;

   Copy

   Because there are millions of rows of data, the COPY process may take a few minutes depending on your internet connection and local client resources.

Note

If you're using a Docker container, add the data files to your container before copying them into your database.

To add files to your container:

docker cp tutorial_sample_tick.csv timescaledb:/tutorial_sample_tick.csv
docker cp tutorial_sample_company.csv timescaledb:/tutorial_sample_company.csv

Copy

Now that you have data in your Timescale instance, learn how to query the data.