A lightweight, high-performance time series data historian built on SQLite for storing and querying historical process data, sensor readings, and metrics.

SQLite Historian provides a simple yet powerful solution for time series data storage and retrieval. It leverages SQLite's reliability and efficiency to create a historian database suitable for:

• Industrial process monitoring
• IoT sensor data collection
• Application metrics and logging
• Environmental monitoring
• Performance tracking

• Fast Time Series Storage: Optimized table structures and indexes for time-based queries
• Efficient Compression: Store millions of data points with minimal disk space
• Flexible Query Interface: Simple API for reading and writing time series data
• Tag-Based Organization: Organize data points by tags/channels
• Aggregation Functions: Built-in support for min, max, avg, sum, and count
• Data Interpolation: Handle missing data with interpolation options
• Batch Operations: Efficient bulk insert and query operations
• Zero Configuration: No server setup required, embedded database
• ACID Compliance: Guaranteed data integrity through SQLite transactions

pip install sqlite-historian

Or install from source:

git clone https://github.com/intigration/sqlite_historian.git cd sqlite_historian pip install -e .

from sqlite_historian import Historian # Create a new historian database hist = Historian('process_data.db')

from datetime import datetime # Write a single data point hist.write('temperature.reactor1', 85.5, datetime.now()) # Write multiple points data_points = [ ('temperature.reactor1', 85.5, datetime(2026, 1, 25, 10, 0, 0)), ('pressure.reactor1', 101.3, datetime(2026, 1, 25, 10, 0, 0)), ('flow.inlet', 150.2, datetime(2026, 1, 25, 10, 0, 0)) ] hist.write_batch(data_points)

from datetime import datetime, timedelta # Read data for a time range start_time = datetime.now() - timedelta(hours=24) end_time = datetime.now() data = hist.read('temperature.reactor1', start_time, end_time) for timestamp, value in data: print(f"{timestamp}: {value}")

# Get hourly averages avg_data = hist.aggregate( 'temperature.reactor1', start_time, end_time, interval='1h', function='avg' ) # Get statistics stats = hist.get_statistics('temperature.reactor1', start_time, end_time) print(f"Min: {stats['min']}, Max: {stats['max']}, Avg: {stats['avg']}")

The historian uses an optimized schema for time series data:

-- Tags/Channels table CREATE TABLE tags ( tag_id INTEGER PRIMARY KEY, tag_name TEXT UNIQUE NOT NULL, description TEXT, units TEXT, data_type TEXT ); -- Time series data table CREATE TABLE data_points ( tag_id INTEGER NOT NULL, timestamp INTEGER NOT NULL, -- Unix timestamp in milliseconds value REAL NOT NULL, quality INTEGER DEFAULT 192, -- OPC quality code PRIMARY KEY (tag_id, timestamp), FOREIGN KEY (tag_id) REFERENCES tags(tag_id) ) WITHOUT ROWID; -- Indexes for fast time-based queries CREATE INDEX idx_timestamp ON data_points(timestamp); CREATE INDEX idx_tag_time ON data_points(tag_id, timestamp);

# Write data with quality indicator hist.write('sensor.reading', 42.5, datetime.now(), quality=192) # Good hist.write('sensor.reading', 0.0, datetime.now(), quality=0) # Bad

# Get interpolated values at specific timestamps timestamps = [datetime(2026, 1, 25, h, 0, 0) for h in range(24)] values = hist.interpolate('temperature.reactor1', timestamps, method='linear')

# Automatically delete data older than specified period hist.set_retention_policy('temperature.*', days=90) hist.apply_retention_policies()

# Export to CSV hist.export_csv('temperature.reactor1', start_time, end_time, 'export.csv') # Export to Pandas DataFrame df = hist.to_dataframe(['temperature.reactor1', 'pressure.reactor1'], start_time, end_time) # Backup database hist.backup('backup_2026-01-25.db')

• Write Performance: ~10,000-50,000 inserts/second with batch operations
• Query Performance: Millions of data points can be queried in milliseconds with proper indexing
• Storage: ~20-30 bytes per data point (varies with compression)
• Database Size: Tested with databases containing 100M+ data points

1. Use batch writes instead of individual inserts
2. Use appropriate time ranges in queries
3. Enable WAL mode for better concurrent access
4. Regularly vacuum the database to reclaim space
5. Use aggregation queries instead of fetching raw data when possible

# Configure historian settings hist = Historian('process_data.db', config={ 'journal_mode': 'WAL', # Write-Ahead Logging 'cache_size': -64000, # 64MB cache 'synchronous': 'NORMAL', # Balance safety/performance 'temp_store': 'MEMORY', # Use memory for temp storage 'auto_vacuum': 'INCREMENTAL' # Automatic space reclamation })

• write(tag, value, timestamp, quality=192) - Write a single data point
• write_batch(data_points) - Write multiple data points efficiently
• read(tag, start_time, end_time, quality_filter=None) - Read raw data
• aggregate(tag, start_time, end_time, interval, function) - Aggregate data
• get_statistics(tag, start_time, end_time) - Get min/max/avg/count
• interpolate(tag, timestamps, method='linear') - Interpolate values
• list_tags(pattern=None) - List available tags
• get_tag_info(tag) - Get metadata for a tag
• delete_data(tag, start_time, end_time) - Delete data points

Monitor and store PLC data, SCADA values, and process variables for analysis and compliance.

Collect sensor readings from IoT devices with efficient local storage and querying.

Track application metrics, performance counters, and system health over time.

Store experimental or simulation data for later analysis with Python data science tools.

Contributions are welcome! Please feel free to submit a Pull Request.

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add some amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

This project is licensed under the MIT License - see the LICENSE file for details.

• Built on top of the excellent SQLite database engine
• Inspired by industrial historian systems and time series databases

• Documentation: https://github.com/intigration/sqlite_historian/wiki
• Issues: https://github.com/intigration/sqlite_historian/issues
• Discussions: https://github.com/intigration/sqlite_historian/discussions

• Support for binary/blob data types
• Built-in data compression options
• Real-time subscription/notification system
• REST API server option
• Grafana data source plugin
• Multi-database federation
• Advanced analytics functions