Add time-series data
To explore TimescaleDB's features, you need some sample data. This tutorial provides real-time stock trade data, also known as tick data, from Twelve Data.
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
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).
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.
|time||timestamptz||Timestamp column incrementing second by second|
|symbol||text||Symbols representing a company, mapped to company names in the |
|price||double precision||Stock quote price for a company at the given timestamp|
|day_volume||int||Number of shares traded each day, NULL values indicate the market is closed|
company: contains a mapping for symbols to company names.
|symbol||text||the symbol representing a company name|
|name||text||Corresponding company name|
To ingest data into the tables that you created, you need to download the dataset and copy the data to your database.
real_time_stock_data.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 the following commands:
\COPY stocks_real_time from './tutorial_sample_tick.csv' DELIMITER ',' CSV HEADER;
\COPY company from './tutorial_sample_company.csv' DELIMITER ',' CSV HEADER;
Because there are millions of rows of data, the
COPYprocess may take a few minutes depending on your internet connection and local client resources.
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
Now that you have data in your TimescaleDB instance, learn how to query the data.
Found an issue on this page?Report an issue!