Back to Blog

Working with Time-Series Data: Patterns and Best Practices

February 2, 2026•By Timothy Murphy
Working with Time-Series Data: Patterns and Best Practices

Every click, every sensor reading, every transaction - it all happens at a specific moment in time. Time-series data is everywhere, and knowing how to handle it efficiently can make or break your application.

What Is Time-Series Data?

Time-series data is any data that's recorded over time. Each record has a timestamp that tells you when something happened.

Common examples:

  • Application metrics - CPU usage, response times, error rates
  • Financial data - Stock prices, transactions, exchange rates
  • IoT sensors - Temperature readings, motion detection, energy usage
  • User analytics - Page views, clicks, session data
  • Log data - Server logs, application events, audit trails

Designing Your Schema

The Basic Structure

At minimum, you need:

CREATE TABLE metrics (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    timestamp DATETIME NOT NULL,
    metric_name VARCHAR(100) NOT NULL,
    value DECIMAL(15, 4) NOT NULL,
    INDEX idx_timestamp (timestamp),
    INDEX idx_metric_time (metric_name, timestamp)
);

A Better Approach: Wide Tables

For better query performance, consider a wider table design:

CREATE TABLE server_metrics (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    timestamp DATETIME NOT NULL,
    server_id INT NOT NULL,
    cpu_percent DECIMAL(5, 2),
    memory_percent DECIMAL(5, 2),
    disk_io_read BIGINT,
    disk_io_write BIGINT,
    network_in BIGINT,
    network_out BIGINT,
    INDEX idx_server_time (server_id, timestamp)
);

This is faster to query because you don't need to JOIN or pivot data.

Using Tags for Flexibility

Add tags or dimensions for filtering:

CREATE TABLE events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    timestamp DATETIME NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    source VARCHAR(100),
    environment VARCHAR(20),  -- 'production', 'staging', 'development'
    region VARCHAR(20),       -- 'us-east', 'eu-west', etc.
    value DECIMAL(15, 4),
    metadata JSON,
    INDEX idx_type_time (event_type, timestamp),
    INDEX idx_env_time (environment, timestamp)
);

Essential Query Patterns

Pattern 1: Recent Data

Get the latest readings:

-- Last 24 hours of CPU metrics
SELECT timestamp, cpu_percent
FROM server_metrics
WHERE server_id = 1
  AND timestamp > NOW() - INTERVAL 24 HOUR
ORDER BY timestamp DESC;

Pattern 2: Time Bucketing

Group data into time intervals:

-- Average CPU per hour for the last week
SELECT
    DATE_FORMAT(timestamp, '%Y-%m-%d %H:00') AS hour,
    AVG(cpu_percent) AS avg_cpu,
    MAX(cpu_percent) AS max_cpu,
    MIN(cpu_percent) AS min_cpu
FROM server_metrics
WHERE server_id = 1
  AND timestamp > NOW() - INTERVAL 7 DAY
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H:00')
ORDER BY hour;

PostgreSQL version:

SELECT
    DATE_TRUNC('hour', timestamp) AS hour,
    AVG(cpu_percent) AS avg_cpu,
    MAX(cpu_percent) AS max_cpu
FROM server_metrics
WHERE server_id = 1
  AND timestamp > NOW() - INTERVAL '7 days'
GROUP BY DATE_TRUNC('hour', timestamp)
ORDER BY hour;

Pattern 3: Moving Averages

Smooth out spiky data:

-- 5-minute moving average (MySQL 8.0+)
SELECT
    timestamp,
    cpu_percent,
    AVG(cpu_percent) OVER (
        ORDER BY timestamp
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM server_metrics
WHERE server_id = 1
  AND timestamp > NOW() - INTERVAL 1 HOUR;

Pattern 4: Detecting Anomalies

Find values outside normal range:

-- Find CPU spikes above 90%
SELECT timestamp, cpu_percent
FROM server_metrics
WHERE server_id = 1
  AND cpu_percent > 90
  AND timestamp > NOW() - INTERVAL 24 HOUR
ORDER BY cpu_percent DESC;

Pattern 5: Rate of Change

Calculate how fast values are changing:

-- Calculate change rate between readings
SELECT
    timestamp,
    value,
    value - LAG(value) OVER (ORDER BY timestamp) AS change,
    timestamp - LAG(timestamp) OVER (ORDER BY timestamp) AS time_diff
FROM metrics
WHERE metric_name = 'requests_total'
  AND timestamp > NOW() - INTERVAL 1 HOUR;

Pattern 6: Gap Detection

Find missing data:

-- Find gaps larger than 5 minutes
WITH time_diffs AS (
    SELECT
        timestamp,
        LAG(timestamp) OVER (ORDER BY timestamp) AS prev_timestamp,
        TIMESTAMPDIFF(MINUTE, LAG(timestamp) OVER (ORDER BY timestamp), timestamp) AS gap_minutes
    FROM server_metrics
    WHERE server_id = 1
      AND timestamp > NOW() - INTERVAL 24 HOUR
)
SELECT timestamp, prev_timestamp, gap_minutes
FROM time_diffs
WHERE gap_minutes > 5;

Performance Optimization

1. Partition by Time

Split your table into time-based partitions:

CREATE TABLE metrics_partitioned (
    id BIGINT AUTO_INCREMENT,
    timestamp DATETIME NOT NULL,
    metric_name VARCHAR(100) NOT NULL,
    value DECIMAL(15, 4) NOT NULL,
    PRIMARY KEY (id, timestamp)
) PARTITION BY RANGE (TO_DAYS(timestamp)) (
    PARTITION p_2025_12 VALUES LESS THAN (TO_DAYS('2026-01-01')),
    PARTITION p_2026_01 VALUES LESS THAN (TO_DAYS('2026-02-01')),
    PARTITION p_2026_02 VALUES LESS THAN (TO_DAYS('2026-03-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Benefits:

  • Queries only scan relevant partitions
  • Easy to delete old data (drop partition)
  • Better cache utilization

2. Use the Right Data Types

-- Use DATETIME instead of VARCHAR for timestamps
timestamp DATETIME NOT NULL  -- Good: 8 bytes, queryable
timestamp VARCHAR(30)        -- Bad: 30 bytes, slower comparisons

-- Use appropriate numeric precision
value DECIMAL(10, 2)  -- Good for money
value FLOAT           -- Good for scientific data (less precision)
value DOUBLE          -- Good for high-precision calculations

3. Batch Your Inserts

Insert multiple rows at once:

-- Slow: Individual inserts
INSERT INTO metrics (timestamp, metric_name, value) VALUES ('2026-01-08 10:00:00', 'cpu', 45.2);
INSERT INTO metrics (timestamp, metric_name, value) VALUES ('2026-01-08 10:00:01', 'cpu', 46.1);

-- Fast: Batch insert
INSERT INTO metrics (timestamp, metric_name, value) VALUES
    ('2026-01-08 10:00:00', 'cpu', 45.2),
    ('2026-01-08 10:00:01', 'cpu', 46.1),
    ('2026-01-08 10:00:02', 'cpu', 44.8),
    ('2026-01-08 10:00:03', 'cpu', 45.5);

4. Create Summary Tables

Pre-aggregate data for faster queries:

-- Create hourly summary table
CREATE TABLE metrics_hourly (
    hour DATETIME NOT NULL,
    metric_name VARCHAR(100) NOT NULL,
    avg_value DECIMAL(15, 4),
    min_value DECIMAL(15, 4),
    max_value DECIMAL(15, 4),
    count INT,
    PRIMARY KEY (hour, metric_name)
);

-- Populate with a scheduled job
INSERT INTO metrics_hourly
SELECT
    DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00') AS hour,
    metric_name,
    AVG(value),
    MIN(value),
    MAX(value),
    COUNT(*)
FROM metrics
WHERE timestamp >= NOW() - INTERVAL 1 HOUR
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00'), metric_name
ON DUPLICATE KEY UPDATE
    avg_value = VALUES(avg_value),
    min_value = VALUES(min_value),
    max_value = VALUES(max_value),
    count = VALUES(count);

Data Retention

Don't keep data forever. Set up automatic cleanup:

-- Delete data older than 90 days
DELETE FROM metrics
WHERE timestamp < NOW() - INTERVAL 90 DAY
LIMIT 10000;  -- Delete in batches to avoid locking

Or with partitioning:

-- Much faster: Drop entire partition
ALTER TABLE metrics_partitioned
DROP PARTITION p_2025_10;

Handling Time Zones

Always store timestamps in UTC:

-- Store in UTC
INSERT INTO events (timestamp, event_type)
VALUES (UTC_TIMESTAMP(), 'user_login');

-- Convert to local time when displaying
SELECT
    CONVERT_TZ(timestamp, 'UTC', 'America/New_York') AS local_time,
    event_type
FROM events;

Common Mistakes to Avoid

1. Using String Timestamps

-- Bad: Stored as string
timestamp VARCHAR(30) DEFAULT '2026-01-08 10:00:00'

-- Good: Proper datetime type
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP

2. Missing Time Index

-- Always index your timestamp column
CREATE INDEX idx_timestamp ON metrics(timestamp);

-- For filtered queries, use composite index
CREATE INDEX idx_metric_time ON metrics(metric_name, timestamp);

3. Querying Without Time Bounds

-- Bad: Scans entire table
SELECT AVG(value) FROM metrics WHERE metric_name = 'cpu';

-- Good: Limited time range
SELECT AVG(value) FROM metrics
WHERE metric_name = 'cpu'
  AND timestamp > NOW() - INTERVAL 1 DAY;

4. Not Using Appropriate Precision

-- Too precise for most use cases
timestamp DATETIME(6)  -- Microsecond precision

-- Usually sufficient
timestamp DATETIME     -- Second precision

When to Consider Specialized Solutions

If your time-series needs outgrow traditional databases, consider:

  • TimescaleDB - PostgreSQL extension optimized for time-series
  • InfluxDB - Purpose-built time-series database
  • Prometheus - Great for metrics and monitoring
  • ClickHouse - Column-oriented, excellent for analytics

Signs you might need a specialized solution:

  • Millions of data points per day
  • Sub-second query requirements on large datasets
  • Complex time-based aggregations at scale

Conclusion

Time-series data is unique, and treating it right pays off in performance and maintainability. Start with good schema design, use appropriate indexes, and implement data retention from day one.

The patterns covered here will handle most use cases. As you scale, consider partitioning and summary tables before jumping to specialized databases.

Ready to Explore Your Time-Series Data?

LunoDB makes it easy to query and visualize time-series data with built-in chart views and date range filters. Download LunoDB and start making sense of your timestamped data.

Related Posts

The Ultimate Guide to Database Backups and Disaster Recovery
February 16, 2026

The Ultimate Guide to Database Backups and Disaster Recovery

Protect your data from disasters with a solid backup strategy. Learn backup types, best practices, and how to create a disaster recovery plan that actually works.

Full-Text Search in SQL Databases: MySQL and PostgreSQL Compared
February 9, 2026

Full-Text Search in SQL Databases: MySQL and PostgreSQL Compared

Build powerful search features directly in your database. Learn how to implement full-text search in MySQL and PostgreSQL, with practical examples and performance tips.

Understanding Database Indexes: The Key to Lightning-Fast Queries
January 26, 2026

Understanding Database Indexes: The Key to Lightning-Fast Queries

Discover how database indexes work and why they're essential for fast queries. Learn when to create indexes, which types to use, and common mistakes to avoid.