Dates and types

A column’s kind comes from its declared type, read from the catalog, never from the values stored in it.

CREATE TABLE work_order (
  id INTEGER PRIMARY KEY,
  opened_at DATETIME,     -- a date: windows, timelines, date filters
  due_on TEXT             -- text, even when every value is '2026-09-24'
);

The rule

The declared type is matched as text, first rule first:

Kind	The declared type contains	Examples
boolean	bool, or an integer a CHECK (flag IN (0, 1)) pins	boolean, BOOL, INTEGER CHECK (auto_renew IN (0, 1))
number	int, serial, numeric, decimal, real, double, float, money	INTEGER, bigint, numeric(12,2), tinyint(1)
date	date or time	DATE, DATETIME, TIMESTAMP, timestamptz, time
text	char, text, uuid, json, enum, clob	varchar(80), TEXT, uuid, jsonb
other	anything else, or nothing	BLOB, bytea, a SQLite column with no type

A dashboard section’s over, a timeline stream’s at and the date presets of a filter need a date column; "created_at" is TEXT, so the time window cannot narrow by it is this rule speaking.

Per engine

• SQLite stores any value in any column, so the declared type in CREATE TABLE is all there is to go on. A column declared TEXT holding ISO strings is text, and INTEGER holding epoch seconds is a number. Declare DATE or DATETIME, or, since SQLite changes a column’s type only by rebuilding the table, let the App say it (below). A 0/1 flag reads as yes or no when it is declared BOOLEAN or a CHECK pins it to 0 and 1 (IN (0, 1), = 0 OR = 1, BETWEEN 0 AND 1); the stored value stays 0 or 1.
• PostgreSQL: date, timestamp, timestamptz and time are dates; interval is other. An enum column’s type reads USER-DEFINED, so it is other too, and its labels are the column’s vocabulary.
• MySQL / MariaDB: DATE, DATETIME, TIMESTAMP and TIME are dates; YEAR is other. BOOLEAN is stored as tinyint(1), so it reads as a number.

Declaring a TEXT column a date (SQLite)

resources: { work_order: { types: { due_on: 'date', closed_at: 'datetime' } } }

The App’s catalog then reads the column as TEXT as date: windows, a timeline’s at, calendars, date presets and date filters take it, and a chart groups it by month. Values must be ISO (2026-09-24, 2026-09-24 14:30:00), which compare as text exactly as a DATE column’s do; anything else matches no window and groups as empty. PostgreSQL and MySQL refuse the declaration: give the column a date type, or cast it in a view.

Numbers that are not JavaScript numbers

PostgreSQL bigint, numeric and money, and MySQL decimal, arrive as strings; a SQLite or MySQL integer arrives as a string once it passes 2^53. They are shown as written, never rounded. The generated Rows type says string (or number | string) for them, so a command handler converts on purpose.