Published at Oct 11, 2023
Time-Series DataWritten by Ana Tavares and Carlota Soto
If you’ve ever heard of Timescale, you know that we’re committed to building a faster, fiercer PostgreSQL to support heavy workloads, including time-series data (or temporal data). However, in conversations with our community members and customers, we often find that people don’t refer to their types of data as time series, even though they often are.
If you want to know if your data is time-series data, this is the litmus test: does your data have some kind of timestamp or time element related to it, even if that may not be its main dimension? If the answer is “yes,” you’re dealing with time-series data.
Time-series data is one of the most common and valuable data types in our increasingly data-driven world. This form of data tracks changes in variables over time, making it essential for understanding trends, patterns, and seasonal variations that might otherwise remain hidden.
Time-series data is ubiquitous across industries and disciplines, and is of the utmost importance as it allows us to do the following things:
Track how variables change over specific time intervals
Identify patterns that emerge only when viewed through a temporal lens
Make data-informed decisions based on historical trends
This data type has become crucial in numerous fields:
In finance, time-series data tracks stock prices, exchange rates, and economic indicators, helping investors and analysts make informed decisions.
Healthcare professionals rely on time-series data to monitor patient vitals, disease progression, and treatment effectiveness over time.
Scientific researchers use time-series data to track environmental changes, experimental results, and natural phenomena that evolve over different timescales.
Time-series data becomes truly valuable when subjected to different analytical approaches:
Forecasting involves projecting future values based on historical time-series data. This analysis uses patterns identified in past data to predict what might happen next. Effective forecasting can help achieve several outcomes:
It can help businesses prepare for seasonal demands.
It enables better resource allocation and planning.
It can provide early warnings for potential issues or opportunities.
Historical analysis examines relationships between variables as they change over time. This approach is powerful:
Reveals correlations and potential causal relationships between different metrics
Identifies how different parts of a system influence each other
Uncovers underlying connections that might not be apparent in static data
By analyzing how variables interact over time, organizations can gain deeper insights into their operations, customers, and environment, leading to more effective strategies and decisions.
If you want to learn more, we covered this topic in the fittingly-named article “What Is a Time Series and How Is It Used?”
Timescale works just like PostgreSQL under the hood (with some cool optimizations engineered by our team to make it blazing-fast), but both databases can handle a broad range of data types, thus meeting the diverse needs of different sectors. In this article, we’ll list many of them, and you can expect this to grow as we add more use cases to our portfolio.
Now, is your data…
PostgreSQL and Timescale can process and store sensor data, making them ideal for IoT applications. For instance, they can capture temperature or humidity sensor readings for analysis or real estate usage data to optimize large real estate footprints, among many other data points collected by IoT sensors.
CREATE TABLE smart_home_data (
timestamp TIMESTAMPTZ NOT NULL,
device_id UUID NOT NULL,
temperature DOUBLE PRECISION,
motion_detected BOOLEAN,
PRIMARY KEY(timestamp, device_id)
);
Transaction data, financial or customer transactions, or order history—whatever you call this data, it is well accommodated by these databases. This category covers everything from purchases to ATM withdrawals to online shopping activities, facilitating thorough user behavior analysis.
CREATE TABLE financial_transactions (
transaction_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL,
timestamp TIMESTAMPTZ NOT NULL,
amount DECIMAL(20,2) NOT NULL,
transaction_type VARCHAR(50) NOT NULL,
description TEXT
);
PostgreSQL and Timescale can manage data from various applications, including operational analytics and real-time applications. This helps in enhancing the efficiency of the apps and troubleshooting issues. Operational analytics, in particular, is a more specific term for analytics that involves the analysis of business operations. It can be applied to various business areas, such as supply chain management, customer service, human resources, production, and logistics.
CREATE TABLE operational_data (
data_id SERIAL PRIMARY KEY,
app_id INT NOT NULL,
timestamp TIMESTAMPTZ NOT NULL,
cpu_usage DECIMAL(5,2) NOT NULL,
memory_usage DECIMAL(5,2) NOT NULL,
response_time INT NOT NULL,
error_rate DEC
);
For transport or delivery companies, fleet data—like vehicle location, condition, and usage statistics—can be well managed by these databases. Delivery vehicles today are fitted with GPS trackers, logging their location and speed at frequent intervals. This allows delivery companies to track routes in real time, optimize driving times, improve safety protocols, and guarantee on-time deliveries. With PostgreSQL/Timescale, managing this data becomes simple and efficient, highlighting the benefits of a technically informed approach to logistics.
CREATE TABLE fleet_data (
record_id SERIAL PRIMARY KEY,
vehicle_id INT NOT NULL,
timestamp TIMESTAMPTZ NOT NULL,
location GEOGRAPHY NOT NULL,
speed DECIMAL(5,2) NOT NULL,
fuel_level DECIMAL(5,2),
engine_status BOOLEAN NOT NULL
);
Metrics data, including energy metrics, can also be handled by PostgreSQL and Timescale, providing vital insight into energy usage patterns and efficiencies. Here’s an example: to enhance energy efficiency, office complexes utilize smart sensors throughout each floor to continuously track electricity usage, lighting levels, temperature, and room occupancy. This system can generate millions of data points in a standard workday.
CREATE TABLE energy_metrics (
metric_id SERIAL PRIMARY KEY,
timestamp TIMESTAMPTZ NOT NULL,
facility_id INT NOT NULL,
power_usage DECIMAL(10,2) NOT NULL,
voltage DECIMAL(10,2) NOT NULL,
current DECIMAL(10,2) NOT NULL,
efficiency DECIMAL(5,2)
);
These databases are equipped to handle fintech data, including trading data and tick data. High-frequency trading applications track price fluctuations of individual stocks or crypto coins multiple times per second, amassing millions of daily records for each symbol. This data analysis allows traders to identify market trends, forecast price changes, and plan their strategies. Additionally, real-time analysis enables immediate responses to market shifts, either capitalizing on opportunities or minimizing risks.
CREATE TABLE tick_data (
tick_id SERIAL PRIMARY KEY,
timestamp TIMESTAMPTZ NOT NULL,
symbol VARCHAR(10) NOT NULL,
price DECIMAL(10,2) NOT NULL,
volume INT NOT NULL
);
PostgreSQL and Timescale can manage event data, capturing every interaction users have with a system or application, and providing substantial insights for enhancing user experience, among other benefits.
Consider an online shopping platform with daily traffic in the millions. Each user interaction—from clicks and searches to adding products to the cart—is meticulously logged with time stamps. Analyzing these events provides valuable insights into user behavior, enabling optimization of their experience, customization of marketing strategies, anomaly detection, and refinement of the platform's functionality. This data-driven approach can significantly enhance engagement and sales.
Another great example is online gaming, where sometimes millions of players interact in the same environment. These interactions generate billions of data points, which can be analyzed to improve the game dynamics and in-game selling.
CREATE TABLE event_data (
event_id SERIAL PRIMARY KEY,
user_id INT NOT NULL,
timestamp TIMESTAMPTZ NOT NULL,
event_type VARCHAR(50) NOT NULL,
details JSONB
);
In the realm of geospatial analytics, these databases can handle vector data. Vector data is a type of geospatial data that represents objects as points, lines, or polygons. It can include information about physical features such as buildings, roads, and bodies of water, as well as abstract features such as administrative boundaries and electoral districts. Vector data is commonly used in Geographic Information Systems (GIS) for spatial analysis, mapping, and visualization.
Weather data refers to information collected and recorded about atmospheric conditions at a specific location and time. This data can include temperature, humidity, wind speed and direction, pressure, precipitation, and other meteorological variables.
Consider a research institute focused on decoding Earth's century-long climate history. They've amassed global hourly weather data, tracking temperature, humidity, rain, and wind speed. This deep dive into past weather trends enables them to identify patterns, link climatic events, forecast future anomalies, and contribute vital insights to the climate change dialogue.
CREATE TABLE weather_data (
record_id SERIAL PRIMARY KEY,
location_id INT NOT NULL,
timestamp TIMESTAMPTZ NOT NULL,
temperature DECIMAL(5,2),
humidity DECIMAL(5,2),
wind_speed DECIMAL(5,2),
wind_direction DECIMAL(5,2),
pressure DECIMAL(7,2),
precipitation DECIMAL(5,2)
);
Insurance data, including policy details, claim history, and customer records, can be effectively managed by these databases for accurate risk assessment and claim processing.
Visualize a leading insurance firm processing thousands of diverse claims daily. Every claim call is thoroughly logged, noting details like call time, duration, claim nature, and caller sentiment patterns. This data allows them to spot claim trends, evaluate their claim processing efficiency, refine customer service strategies, identify potential fraud, and ultimately enhance operations for more effective and efficient policyholder service.
CREATE TABLE insurance_data (
record_id SERIAL PRIMARY KEY,
policy_number VARCHAR(50) NOT NULL,
customer_id INT NOT NULL,
issue_date TIMESTAMPTZ NOT NULL,
expiry_date TIMESTAMPTZ,
coverage_type VARCHAR(50) NOT NULL,
coverage_amount DECIMAL(20,2) NOT NULL,
premium DECIMAL(10,2) NOT NULL,
claim_status VARCHAR(50)
);
For telecommunication companies, these databases offer efficient handling of call records, aiding in billing, fraud detection, and network optimization.
CREATE TABLE call_records (
call_id SERIAL PRIMARY KEY,
caller_id INT NOT NULL,
receiver_id INT NOT NULL,
start_time TIMESTAMPTZ NOT NULL,
end_time TIMESTAMPTZ NOT NULL,
call_duration INT NOT NULL,
call_type VARCHAR(20) NOT NULL,
location GEOGRAPHY
);
So, maybe you didn’t think you had time-series data, and this article changed your mind. Maybe you know you have a time-series problem, but you call it a different way (any of the above).
Either way, remember that PostgreSQL and Timescale support all these types of data, making them a versatile choice for various industries. While they provide a flexible and reliable solution to many data management needs, Timescale has the upper hand, allowing you to store and query your data alongside your historical data. Just take a look at some of our examples of time-series analysis.
And if you need to scale PostgreSQL, there are other ways we can help you. 😼 Are your queries getting slower and sluggish? Is your database performance degrading? Check out how you can solve this problem with automatic Postgres partitioning via hypertables.