Data Types

Teide uses a columnar storage engine with fixed-width types optimized for analytical workloads. Each column stores values of a single type, enabling efficient compression and vectorized operations.

Supported Types

Internal Storage Details

DATE

Stored as a signed 32-bit integer representing the number of days since the epoch 2000-01-01. Dates before the epoch are negative values.

-- 2000-01-01 is stored as 0
-- 2024-06-15 is stored as 8932
-- 1999-12-31 is stored as -1
SELECT DATE '2024-06-15';
SELECT CAST('2024-06-15' AS DATE);

TIME

Stored as a signed 32-bit integer representing milliseconds since midnight. Valid range: 0 to 86,399,999.

-- 00:00:00 is stored as 0
-- 12:30:00 is stored as 45000000
-- 23:59:59.999 is stored as 86399999
SELECT TIME '12:30:00';
SELECT CAST('14:30:00' AS TIME);

TIMESTAMP

Stored as a signed 64-bit integer representing microseconds since 2000-01-01 00:00:00. This provides microsecond precision over a range of approximately +/- 292,000 years.

SELECT TIMESTAMP '2024-06-15 12:30:00';
SELECT CAST('2024-06-15 14:30:00' AS TIMESTAMP);

SYM (Dictionary-Encoded Strings)

The SYM type stores strings using dictionary encoding. Each unique string is assigned an integer index in a global symbol table. Columns store the integer index rather than the full string, making equality checks and GROUP BY operations extremely fast.

CREATE TABLE events (
  id INTEGER,
  category SYM,
  status SYM
);

-- SYM columns are ideal for low-cardinality string columns
-- like status codes, categories, region names, etc.

Type Casting

Use CAST to convert between types:

-- String to numeric
SELECT CAST('42' AS INTEGER);
SELECT CAST('3.14' AS REAL);

-- Numeric to string
SELECT CAST(42 AS VARCHAR);

-- String to date/time
SELECT CAST('2024-06-15' AS DATE);
SELECT CAST('12:30:00' AS TIME);
SELECT CAST('2024-06-15 12:30:00' AS TIMESTAMP);

-- Integer to boolean
SELECT CAST(1 AS BOOLEAN);   -- true
SELECT CAST(0 AS BOOLEAN);   -- false

-- Boolean to integer
SELECT CAST(TRUE AS INTEGER);  -- 1
SELECT CAST(FALSE AS INTEGER); -- 0

NULL Values

All types support NULL values. A NULL represents a missing or unknown value. NULL propagates through expressions: any arithmetic or comparison involving NULL yields NULL (with specific exceptions like IS NULL and COALESCE).

SELECT NULL;
SELECT 1 + NULL;          -- NULL
SELECT NULL = NULL;        -- NULL (not TRUE)
SELECT NULL IS NULL;       -- TRUE
SELECT COALESCE(NULL, 42); -- 42

Literals