Skip to main content

SQL Reference (Website Summary)

This page summarizes the SQL surface used on the website. For the full engine reference, see docs/sql.md in the source repository.

The time column​

  • Every table must include a column named time.
  • Type: BIGINT or TIMESTAMP (various precisions / time zones).
  • Always non-null.
  • Used for memtable ordering and Parquet time pruning.

Common types​

Numeric & Boolean​

SQL TypeNotes
TINYINT / SMALLINT / INT / BIGINTSigned integers
TINYINT UNSIGNED … BIGINT UNSIGNEDUnsigned variants
FLOAT / DOUBLEFloating point
BOOLEAN
DECIMAL(p,s) / NUMERIC(p,s)Precision 1..=38; bare DECIMAL defaults to (38,10)

Strings & Binary​

SQL TypeNotes
VARCHAR / CHAR / TEXTUTF-8 text
BLOB / BINARY / VARBINARY / BYTESBinary

Date & Timestamp​

SQL TypeNotes
DATEInsert as 'YYYY-MM-DD' strings
TIMESTAMP / TIMESTAMP(p)Default milliseconds; INSERT uses raw integer epochs
TIMESTAMP … WITH TIME ZONEStored as UTC

Nested​

SQL TypeNotes
ENUM('a','b',…)Dictionary-backed enum
ARRAY<T> / T[]Lists, e.g. ARRAY<VARCHAR>
STRUCT<field T, …>Nested structs

Unsupported examples: UUID, JSON, SQL Interval type.

DDL / DML highlights​

CREATE TABLE metrics (
  time BIGINT,
  device_id VARCHAR,
  value DOUBLE
);

ALTER TABLE metrics ADD COLUMN region VARCHAR;

INSERT INTO metrics (time, device_id, value)
VALUES (1718000000000, 'sensor-1', 21.5);

LOAD PARQUET '/data/import/part-000.parquet' INTO metrics;
FLUSH TABLE metrics;
FLUSH TABLES;

Timestamp inserts​

For TIMESTAMP columns, INSERT VALUES expects raw integer epochs matching column precision. ISO-8601 strings are not accepted today.

Querying​

DataFusion-powered SELECT with filters, aggregates, joins, and window functions (see engine / IT coverage). Prefer filtering on time for pruning.

SELECT device_id, COUNT(*) AS n, AVG(value) AS avg_v
FROM metrics
WHERE time >= 1718000000000
GROUP BY device_id;

Access path​

All SQL goes through gRPC (monots CLI or Rust SDK), default http://127.0.0.1:50051 with admin / admin.