Dates, Times, Booleans, Money, UUIDs, and Other Logical Types

Learning outcomes

A logical type is the application meaning of a value: timestamp, Boolean flag, money amount, UUID, status code, and so on. SQLite does not need a separate storage class for every logical type. Instead, you choose one of its physical representations and define the conventions that make the value meaningful.

Physical representation and logical meaning are different layers

The five storage classes tell SQLite how a runtime value is represented. They do not tell your application whether INTEGER 1 means “true,” one millisecond, one cent, one retry, or one enum member. A robust schema therefore documents both layers.

The design question is not “which SQLite type is the date type?” There is no dedicated date storage class. The question is “which representation best supports our comparisons, sorting, arithmetic, portability, debugging, and application drivers?”

Dates and times have three conventional SQLite representations

SQLite’s built-in date/time functions understand documented time-value formats rather than a dedicated DATETIME storage class. The three principal representations are ISO-8601-style TEXT, Julian-day REAL, and Unix-time INTEGER (or numeric Unix timestamps when functions are used with the appropriate modifier).

SELECT date('2026-08-12') AS day_text,       datetime('2026-08-12 05:00:00') AS date_time_text,       julianday('2026-08-12 05:00:00') AS julian_value,       unixepoch('2026-08-12 05:00:00') AS unix_seconds;

On the documented Gregorian/UTC-oriented date-function model, the fixed example yields Unix seconds 1786510800 and a Julian-day value near 2461264.7083333335. Do not make tests depend on 'now' when teaching representation; fixed inputs are reproducible.

Choose a date contract before choosing convenience functions

For FieldNotes, suppose maintenance timestamps are exchanged with APIs, read by humans, and sorted frequently, while sub-second arithmetic is not a dominant requirement. Canonical UTC TEXT can be a reasonable choice—but only if the contract fixes the format.

CREATE TABLE IF NOT EXISTS maintenance_event (    event_id    INTEGER PRIMARY KEY,    device_code TEXT NOT NULL,    occurred_at TEXT NOT NULL);INSERT INTO maintenance_event(device_code, occurred_at) VALUES    ('PUMP-007', '2026-08-12T02:00:00Z'),    ('PUMP-007', '2026-08-12T05:30:00Z');SELECT device_code, occurred_atFROM maintenance_eventORDER BY occurred_at;

The ordering property depends on a consistent year-to-fractional-second layout and one timezone convention. Mixing local times, offsets, missing seconds, and different precision can defeat simple lexical reasoning even if each string looks date-like. If your workload centers on arithmetic or an external system already mandates epoch values, INTEGER may be the better contract.

Boolean meaning is convention plus validation

SQLite has no separate Boolean storage class. The SQL keywords TRUE and FALSE normally act as aliases for integer 1 and 0. They improve readability, but they do not create a new runtime class.

SELECT TRUE,  typeof(TRUE),       FALSE, typeof(FALSE);DROP TABLE IF EXISTS device_flag;CREATE TABLE device_flag (    device_code TEXT PRIMARY KEY,    is_active   INTEGER NOT NULL CHECK (is_active IN (0,1))) STRICT;INSERT INTO device_flag VALUES ('PUMP-007', TRUE);INSERT INTO device_flag VALUES ('FAN-014', FALSE);SELECT device_code, is_active, typeof(is_active)FROM device_flagORDER BY device_code;

The stored class is INTEGER. STRICT ensures the field is integer-compatible; the CHECK constraint narrows the domain to 0 or 1. Without that CHECK, INTEGER 2 would still be type-correct but would violate the intended Boolean contract.

Exact money needs an explicit exactness strategy

SQLite REAL values are floating point. Floating point is excellent for many measurements, but binary floating point cannot represent every decimal fraction exactly. If a currency amount must be exact to a defined minor unit, storing the smallest unit as INTEGER is often simpler and safer.

SELECT (0.1 + 0.2) = 0.3 AS exact_float_equality;DROP TABLE IF EXISTS service_charge;CREATE TABLE service_charge (    charge_id    INTEGER PRIMARY KEY,    currency     TEXT NOT NULL,    amount_minor INTEGER NOT NULL CHECK (amount_minor >= 0)) STRICT;-- 47.49 in a currency whose contract defines 100 minor units per major unit:INSERT INTO service_charge(currency, amount_minor)VALUES ('USD', 4749);SELECT currency, amount_minorFROM service_charge;

The first query demonstrates why floating-point equality is not an exact-decimal contract: the expression evaluates false on ordinary IEEE representation. The integer strategy is only correct if you define the currency and scale. Some currencies do not use two decimal minor units, and some domains need more precision than currency cash units.

UUIDs can be readable TEXT or compact 16-byte BLOBs

A UUID is logically a 128-bit identifier. SQLite has no UUID storage class, so a common choice is canonical 36-character text with hyphens or the corresponding 16 raw bytes. Neither choice is universally superior.

SELECT    length('550e8400-e29b-41d4-a716-446655440000') AS text_chars,    typeof('550e8400-e29b-41d4-a716-446655440000') AS text_type,    length(x'550e8400e29b41d4a716446655440000') AS blob_bytes,    typeof(x'550e8400e29b41d4a716446655440000') AS blob_type,    lower(hex(x'550e8400e29b41d4a716446655440000')) AS blob_hex;

The canonical text form is easy to log, copy, inspect, and interchange. A 16-byte BLOB is smaller and avoids textual punctuation, but every application/driver must agree on byte serialization and convert for display. If external APIs already use canonical UUID strings and database size is not dominated by UUID storage, TEXT can reduce integration friction.

Other logical types need the same design discipline

The pattern generalizes. A status can be TEXT with a CHECK list, or INTEGER with an explicit code map. An IP address can be canonical TEXT for readability or binary for compact network-aware processing. Durations can be INTEGER milliseconds, INTEGER nanoseconds, or REAL seconds depending on precision and arithmetic requirements. JSON receives dedicated treatment in Chapter 13 because JSON validity and query behavior add another layer.

Lab: define FieldNotes logical-type contracts

Build a small STRICT table whose physical types are simple but whose logical contract is explicit in column names and constraints. Then verify the runtime classes.

DROP TABLE IF EXISTS field_measurement;CREATE TABLE field_measurement (    measurement_id INTEGER PRIMARY KEY,    device_code    TEXT NOT NULL,    observed_at    TEXT NOT NULL,    is_valid       INTEGER NOT NULL CHECK (is_valid IN (0,1)),    reading_value  REAL,    service_minor  INTEGER CHECK (service_minor IS NULL OR service_minor >= 0),    currency       TEXT,    trace_uuid     TEXT NOT NULL) STRICT;INSERT INTO field_measurement(    device_code, observed_at, is_valid,    reading_value, service_minor, currency, trace_uuid) VALUES (    'SENS-003', '2026-08-12T05:45:00Z', TRUE,    21.75, 1299, 'USD', '550e8400-e29b-41d4-a716-446655440000');SELECT typeof(observed_at),       typeof(is_valid),       typeof(reading_value),       typeof(service_minor),       typeof(trace_uuid)FROM field_measurement;

Expected classes are TEXT, INTEGER, REAL, INTEGER, and TEXT. Then write the missing application contract beside the DDL: observed_at is canonical UTC ISO text; is_valid is 0/1; reading_value uses the unit defined for the device; service_minor is in the minor units of currency; and trace_uuid uses canonical lowercase-or-case-insensitive UUID text according to the API contract.

Failure patterns and safe corrections

Summary and bridge to cross-language boundaries

SQLite gives you a small set of physical value classes and expects the application/schema contract to define higher-level meaning. Dates can be canonical TEXT, Unix-time INTEGER, or Julian-day REAL; Booleans are commonly 0/1 INTEGER values; exact money needs an explicit exactness/scale strategy; UUIDs can be readable TEXT or compact BLOBs. Lesson 5 follows these values across the final boundary: NULL, bytes, Unicode text, collations, and host-language adapters.

References

• SQLite — Datatypes In SQLite.
• SQLite — Date And Time Functions.
• SQLite — Quirks, Caveats, and Gotchas.
• SQLite — STRICT Tables.
• SQLite — Core Functions.
• SQLite — Release History.