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.
Ingesting the dataset
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 TimescleDB 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!