Text, dates and JSON

Text

the functions you will actually use

length(name)                  -- characters, not bytes, in SQLite and Postgres
upper(name) · lower(name)
trim(name) · ltrim · rtrim
substr(placed, 1, 4)          -- from position 1, four characters (1-based!)
replace(plate, '-', '/')
name || ' — ' || style        -- concatenation
instr(name, 'e')              -- position, or 0. Postgres: position('e' in name)
printf('%.2f', price)         -- formatting. Postgres: format()

SQL counts from 1

substr(s, 1, 4) is the first four characters. Every string position in SQL is 1-based, which is the opposite of every language you will call it from.

Comparing text you did not write

Case and whitespace are the two silent killers of a where clause. 'Baku' and 'baku ' are different values in every database. Normalise on the way in — trim, and pick a case — or compare with lower(trim(col)) = lower(trim(?)) and accept that it costs the index.

Collation decides sorting and, in MySQL, equality: a _ci collation makes = case-insensitive by default, which surprises everyone arriving from PostgreSQL. SQLite has collate nocase per column or per comparison.

Time: store UTC, in a format that sorts

PostgreSQL has real types — use timestamptz, always, and date_trunc('month', placed) instead of strftime. MySQL has datetime and date_format. The rule that does not change: store UTC, convert to the reader's zone at the edge, and never store a local time without its offset.

SQLite has no date type — ISO text is the type

'2024-03-14'                 -- sorts correctly as text, forever
'2024-03-14T09:30:00Z'       -- and so does this

date('now')                        -- 2026-09-21
date(placed, '+21 days')           -- add an interval
date(placed, 'start of month')
strftime('%Y-%m', placed)          -- 2024-03  — the grouping key
julianday(b) - julianday(a)        -- days between, as a number
cast(strftime('%s', placed) as integer)   -- unix seconds

Why ISO text works

Left-padded, most significant first: text order and time order are the same. '14/03/2024' sorts by day of the month, which is how a report ends up in a nonsensical order and nobody can see why.

Dates in a where clause

a range beats a function, every time

where strftime('%Y', placed) = '2024'                       -- scan: the index is hidden
where placed >= '2024-01-01' and placed < '2025-01-01'      -- search

JSON: the column that saves you a table

PostgreSQL uses jsonb with ->, ->> and @>, and can index inside it with GIN. MySQL 8 has a json type. The shapes are the same: extract a path, expand an array into rows, index what you filter on.

SQLite (3.38+) and PostgreSQL both do this well

create table prefs (customer_id integer primary key, doc text);

insert into prefs values (1, '{"contact":{"email":"hi@arclith.az"},"tags":["vip","trade"]}');

select json_extract(doc, '$.contact.email')  as email,        -- hi@arclith.az
       json_array_length(doc, '$.tags')      as tags,         -- 2
       doc ->> '$.contact.email'             as same_thing    -- shorthand
from   prefs;

-- one row per tag
select p.customer_id, t.value as tag
from   prefs p, json_each(p.doc, '$.tags') t;

When JSON is the wrong answer

A JSON column is right for genuinely variable, rarely queried data: a settings blob, a webhook payload you keep for audit, per-customer preferences nobody reports on.

It is wrong for anything you filter, join or aggregate on every day. There are no foreign keys inside JSON, no not null, no type checking, and a typo in a key name is a silent NULL rather than an error. If you find yourself writing json_extract in a where clause every morning, that field wanted to be a column.