Write, Run & Share QuestDB queries online using OneCompiler's QuestDB online editor for free. It's one of the robust, feature-rich online editors for QuestDB. Getting started with the OneCompiler's QuestDB editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'QuestDB' and start writing queries to learn and test online without worrying about tedious process of installation.
QuestDB is an open-source, high-performance time-series database designed for fast ingestion and SQL queries. It's optimized for handling timestamped data like financial market data, IoT sensor readings, application metrics, and logs. QuestDB uses a column-oriented storage model and supports standard SQL with extensions for time-series analysis like SAMPLE BY for downsampling data.
Creates a new table with specified columns and data types. For time-series data, designate a timestamp column using TIMESTAMP(column_name) which enables time-based partitioning and optimized time-range queries. QuestDB supports types like STRING, DOUBLE, LONG, INT, BOOLEAN, TIMESTAMP, and DATE. The designated timestamp column is crucial for time-series operations.
CREATE TABLE sensors (
name STRING,
reading DOUBLE,
ts TIMESTAMP
) TIMESTAMP(ts);
-- Table with partition
CREATE TABLE trades (
symbol STRING,
price DOUBLE,
amount DOUBLE,
ts TIMESTAMP
) TIMESTAMP(ts) PARTITION BY DAY;
Adds new rows to a table. For tables with a designated timestamp, rows should be inserted in chronological order for optimal performance. QuestDB is optimized for high-throughput ingestion - it can handle millions of rows per second. Use batch inserts when loading historical data, and ensure timestamps are in ascending order.
INSERT INTO sensors VALUES ('Temperature', 23.5, now());
INSERT INTO sensors VALUES ('Humidity', 65.2, now());
INSERT INTO sensors VALUES ('Pressure', 1013.25, now());
-- Insert with explicit timestamp
INSERT INTO sensors VALUES ('Temperature', 24.1, '2024-01-15T10:30:00Z');
Retrieves data from tables with powerful filtering and time-based operations. QuestDB is optimized for fast analytical queries on time-series data. Use WHERE clauses with timestamp ranges for efficient queries as QuestDB can quickly locate data using its time-partitioned storage. The LATEST BY clause is unique to QuestDB for getting the most recent values.
-- Select all
SELECT * FROM sensors;
-- Select with condition
SELECT * FROM sensors WHERE name = 'Temperature';
-- Select with time range
SELECT * FROM sensors
WHERE ts > '2024-01-01' AND ts < '2024-01-31';
-- Get latest reading per sensor
SELECT * FROM sensors LATEST BY name;
-- Order by timestamp
SELECT * FROM sensors ORDER BY ts DESC LIMIT 10;
A QuestDB-specific clause that returns the latest row for each unique value in specified columns. This is extremely useful for getting current state from time-series data without expensive GROUP BY operations. LATEST BY is optimized internally and much faster than equivalent subqueries or window functions for this use case.
-- Latest reading for each sensor
SELECT * FROM sensors LATEST BY name;
-- Latest trade for each symbol
SELECT * FROM trades LATEST BY symbol;
-- Latest with filter
SELECT * FROM sensors
WHERE reading > 20
LATEST BY name;
Downsamples time-series data by aggregating rows into time buckets. This is essential for analyzing trends over time without processing every data point. SAMPLE BY supports various time units (s, m, h, d, M, y) and works with aggregate functions. Use FILL to handle missing time periods with different strategies.
-- Average reading per hour
SELECT ts, AVG(reading) as avg_reading
FROM sensors
WHERE name = 'Temperature'
SAMPLE BY 1h;
-- Multiple aggregates per 15 minutes
SELECT ts, name,
AVG(reading) as avg_val,
MIN(reading) as min_val,
MAX(reading) as max_val
FROM sensors
SAMPLE BY 15m;
-- Fill missing periods
SELECT ts, AVG(reading)
FROM sensors
SAMPLE BY 1h FILL(NULL);
QuestDB supports standard SQL aggregate functions optimized for time-series workloads. Functions include COUNT, SUM, AVG, MIN, MAX, and statistical functions. Combined with SAMPLE BY, these enable powerful time-series analysis. QuestDB processes aggregations very efficiently using its columnar storage and SIMD instructions.
-- Basic aggregates
SELECT
COUNT(*) as total_readings,
AVG(reading) as avg_reading,
MIN(reading) as min_reading,
MAX(reading) as max_reading
FROM sensors
WHERE name = 'Temperature';
-- Group by
SELECT name, COUNT(*) as readings, AVG(reading) as avg
FROM sensors
GROUP BY name;
QuestDB excels at timestamp-based filtering. Use standard comparison operators or special functions for time ranges. QuestDB's time-partitioned storage makes these queries extremely efficient - it only scans relevant partitions. Support for relative time expressions makes it easy to query recent data.
-- Exact time range
SELECT * FROM sensors
WHERE ts >= '2024-01-15T00:00:00Z'
AND ts < '2024-01-16T00:00:00Z';
-- Using now() for relative queries
SELECT * FROM sensors
WHERE ts > dateadd('h', -24, now());
-- Last 7 days
SELECT * FROM sensors
WHERE ts > dateadd('d', -7, now());
QuestDB supports INNER, LEFT, OUTER, CROSS, and ASOF joins. ASOF JOIN is particularly useful for time-series data - it joins rows based on the closest timestamp match rather than exact equality. This is perfect for correlating events that happen at slightly different times or joining data with different sampling frequencies.
CREATE TABLE quotes (
symbol STRING,
bid DOUBLE,
ask DOUBLE,
ts TIMESTAMP
) TIMESTAMP(ts);
CREATE TABLE trades (
symbol STRING,
price DOUBLE,
ts TIMESTAMP
) TIMESTAMP(ts);
-- Regular join
SELECT t.symbol, t.price, q.bid, q.ask
FROM trades t
INNER JOIN quotes q ON t.symbol = q.symbol;
-- ASOF join (match by closest timestamp)
SELECT t.symbol, t.price, q.bid, q.ask
FROM trades t
ASOF JOIN quotes q ON t.symbol = q.symbol;
QuestDB provides string functions for text manipulation and pattern matching. Common functions include LENGTH, CONCAT, SUBSTRING, UPPER, LOWER, and TRIM. For pattern matching, use the ~ operator for regex or LIKE for simple patterns. These functions work efficiently on QuestDB's columnar storage.
SELECT
UPPER('hello') as upper_case,
LOWER('HELLO') as lower_case,
LENGTH('hello') as len,
CONCAT('Hello', ' ', 'World') as combined,
SUBSTRING('Hello World', 1, 5) as sub;
-- Pattern matching
SELECT * FROM sensors WHERE name ~ 'Temp.*';
SELECT * FROM sensors WHERE name LIKE 'Temp%';
QuestDB has extensive date and time functions for working with timestamps. Use NOW() for current time, DATEADD for date arithmetic, and extraction functions for getting date parts. These functions are essential for time-series analysis and work efficiently with QuestDB's timestamp-optimized storage.
SELECT
NOW() as current_time,
TODAY() as current_date,
DATEADD('d', 7, NOW()) as next_week,
DATEADD('h', -24, NOW()) as yesterday,
YEAR(NOW()) as year,
MONTH(NOW()) as month,
DAY(NOW()) as day,
HOUR(NOW()) as hour;
-- Extract from timestamp column
SELECT ts, YEAR(ts), MONTH(ts), DAY(ts) FROM sensors;
Modify table structure or remove tables. ALTER TABLE in QuestDB supports adding columns and changing parameters. DROP TABLE removes the table and all its data. QuestDB also supports TRUNCATE for removing all data while keeping the table structure, which is useful for clearing test data.
-- Add column
ALTER TABLE sensors ADD COLUMN location STRING;
-- Drop table
DROP TABLE sensors;
-- Drop if exists
DROP TABLE IF EXISTS sensors;
-- Truncate (remove all data)
TRUNCATE TABLE sensors;
