skip to content

In SQLite, what are the five storage classes, and how are booleans and dates stored without dedicated types?

level: juniorimportance: must knowfreq 70%

answer

  1. the value carries its own type
  2. five classes, one of them NULL
  3. audit a column with typeof()
  4. TRUE is just another spelling
  5. three kinds of time-value

basics

~20 s

SQLite stores every value as NULL, INTEGER, REAL, TEXT or BLOB, and the class belongs to the value, not the column. Booleans are the integers 0 and 1; dates are ISO-8601 text, Julian-day reals or Unix-time integers.

solid answer

~40 s

SQLite has five storage classes: `NULL`, `INTEGER` (a signed integer kept in 0 to 8 bytes depending on its magnitude), `REAL` (an 8-byte IEEE float), `TEXT` (a string in the database encoding) and `BLOB` (bytes stored exactly as given). The class is attached to each value, so in an ordinary table two rows of one column can hold different classes, and `typeof(x)` tells you which one a value has. There is no BOOLEAN class: `TRUE` and `FALSE` are just other spellings of 1 and 0. There is no DATETIME class either: the date and time functions work with ISO-8601 text such as `'2026-03-15 09:30:00'`, Julian day numbers stored as REAL, or Unix time stored as INTEGER, and the application picks one format per column.

code

sql · 8 lines
sql
CREATE TABLE events(id INTEGER PRIMARY KEY, flag, happened_at);
INSERT INTO events(flag, happened_at) VALUES (TRUE, '2026-03-15 09:30:00');
INSERT INTO events(flag, happened_at) VALUES (0, unixepoch('2026-03-15 09:30:00'));
INSERT INTO events(flag, happened_at) VALUES (1, julianday('2026-03-15 09:30:00'));
SELECT id, typeof(flag), typeof(happened_at) FROM events;
-- 1|integer|text
-- 2|integer|integer
-- 3|integer|real

go deeper

for a junior

Name the five storage classes and say that the class belongs to the value. Know that booleans are 0 and 1 and that dates are text, a Julian real or a Unix integer.

for a middle

Explain how typeof() exposes mixed classes in one column, how ORDER BY ranks classes, and why a date column needs one representation for comparisons to work.

for a senior

Show how you would audit an inherited database for mixed date formats and boolean junk, and how a CHECK constraint or a STRICT table stops it recurring.

for a principal

Be ready to set a team convention for dates and flags in SQLite schemas, weighing readable ISO text against compact Unix integers and portability to other engines.

## Values carry types, columns only suggest them Most SQL engines use **static typing**: the column decides the type, and a value that does not fit is converted or rejected. SQLite uses **dynamic typing**: the type belongs to each **value**. In an ordinary (non-STRICT) table, any column except an `INTEGER PRIMARY KEY` can hold a value of any type. The declared type of a column only sets a preference, called its *type affinity*, which decides whether SQLite tries to convert a value on the way in. To talk precisely about what is actually stored, SQLite uses the term **storage class**. ## The five storage classes | Storage class | What it holds | |---|---| | `NULL` | the NULL value | | `INTEGER` | a signed integer, stored in 0, 1, 2, 3, 4, 6 or 8 bytes depending on its magnitude | | `REAL` | an 8-byte IEEE 754 floating-point number | | `TEXT` | a string in the database encoding (UTF-8, UTF-16BE or UTF-16LE) | | `BLOB` | a run of bytes, stored exactly as it was given | Several consequences follow from this design: - A storage class is broader than a datatype. The `INTEGER` class covers several on-disk widths, but once a value is read into memory it becomes an 8-byte signed integer, so in everyday use "storage class" and "datatype" mean the same thing. - Two rows of the same column can hold different classes. The function `typeof(x)` returns `'null'`, `'integer'`, `'real'`, `'text'` or `'blob'` for a value, which makes it the first tool for auditing what a column really contains. - The class decides how values sort. `ORDER BY` puts NULLs first, then INTEGER and REAL values together in numeric order, then TEXT in collation order, then BLOBs, and it converts nothing before sorting. - Literals and bound parameters have a class too: `42` is an INTEGER, `'42'` is TEXT, `4.2` is a REAL and `x'2A'` is a BLOB. ## Booleans are integers SQLite has no BOOLEAN storage class. True and false are stored as the integers `1` and `0`. The keywords `TRUE` and `FALSE` are accepted, but they are only other spellings of the integer literals 1 and 0, so `typeof(TRUE)` returns `'integer'`. (If a table has a column actually named `true` or `false`, the word refers to that column instead.) A column declared `BOOLEAN` is accepted as well, because SQLite accepts almost any type name, but the name enforces nothing. In an ordinary table such a column will store `2`, `'yes'` or the text `'true'` without complaint. If only 0 and 1 are acceptable, say so with a constraint such as `CHECK (flag IN (0, 1))`. ## Dates and times have no class of their own There is no DATETIME storage class either. The built-in date and time functions work with three representations, which the documentation calls *time-values*: 1. **TEXT** in ISO-8601 form, `'YYYY-MM-DD HH:MM:SS.SSS'`, for example `'2026-03-15 09:30:00'`. 2. **REAL** as a Julian day number: the days, including fractions, since noon in Greenwich on 24 November 4714 BC in the proleptic Gregorian calendar. 3. **INTEGER** as Unix time: the seconds since `1970-01-01 00:00:00` UTC. The functions `date()`, `time()`, `datetime()`, `julianday()`, `unixepoch()` and `strftime()` convert between these forms. ISO-8601 text and Julian day numbers are read directly; a number is read as Unix time when it is followed by the `'unixepoch'` modifier, while the `'auto'` modifier picks Julian day or Unix time from the number's magnitude. The functions work in UTC internally. They understand only the ISO-8601 layouts the documentation lists, so a spreadsheet export stored as the text `'03/15/2026'` makes `date()` return NULL. Which representation to use is the application's choice: - **ISO text** is readable in any tool and sorts correctly as text, provided every value uses the same zero-padded layout. - **Unix integers** are compact and easy to subtract to get a duration in seconds. - **Julian reals** suit arithmetic in fractional days, such as `julianday(b) - julianday(a)`. The one real mistake is mixing forms in a single column. Because every number sorts before every string, a column holding both Unix integers and ISO strings does not sort by time, and range filters written for one form silently miss the other. ## What interviewers listen for - That the type lives on the value, and that `typeof()` shows it. - That BOOLEAN and DATETIME are names SQLite accepts, not storage classes it has or enforces. - That a date column needs one deliberate format, chosen so the date functions, comparisons and `ORDER BY` all behave. - That a table declared `STRICT` enforces a declared type per column, which is the opt-in answer for teams that want rigid typing.

  • Why does a SQLite column that mixes ISO-8601 text and Unix-time integers sort badly?
    `ORDER BY` converts nothing before sorting and places every INTEGER or REAL value ahead of every TEXT value. So all the Unix-time rows come first in numeric order, then all the ISO strings in text order, whatever the actual dates are. Range filters break the same way. Pick one representation per column and convert the strays with the date functions.
  • Does an INTEGER value always take 8 bytes in a SQLite database file?
    No. The INTEGER storage class is stored in 0, 1, 2, 3, 4, 6 or 8 bytes depending on the magnitude of the value, so small numbers are compact on disk. Once read into memory every integer becomes an 8-byte signed integer, which is why SQL code never sees the difference.

  • SQLite has a DATETIME type that validates dates on insert
  • A BOOLEAN column stores true and false as a separate storage class
  • Every value in an ordinary SQLite column shares the column's declared type
  • TRUE and FALSE are stored as the text 'true' and 'false'
  • The SQLite date functions only work on dates stored as text