Dates in Databases: SQLite TEXT vs INTEGER vs REAL

Series: Dates & Time Zones lecture 5/6 (advanced). For developers who already write SQL and now have to decide what a created_at column really contains. After this lecture you can pick a storage format for time with measured trade-offs, spot the three bugs that silently corrupt time data, and keep a JSON API honest about offsets.

If you maintain a table where "the report for March 10" is missing a few rows, or where the newest order is not at the top of the list, this lecture is for you. Time bugs in databases rarely throw an error: the query runs, returns rows, and the rows are wrong. We store the same 1,000,000 instants three ways in SQLite, measure size and speed, then break things on purpose: mixed offsets, T versus space, impossible dates, a 32-bit counter in C, and a JSON payload whose meaning depends on the server's time zone.

Environment: SQLite 3.51.0 (the sqlite3 CLI and Python's sqlite3 module both report 3.51.0), Python 3.9.6 with system tzdata 2026d, Python 3.12 only for one deprecation warning, Apple clang 21, JavaScriptCore jsc, Apple Silicon Mac (M1 Pro). Every command sets its zone explicitly (TZ=UTC, TZ=Asia/Tokyo and so on); outputs are pasted as printed, paths shortened to ~/demo/. If SQL itself is new, start with SQL basics and indexes and EXPLAIN QUERY PLAN.

SQLite has no date type: three ways to store an instant

SQLite has five storage classes (NULL, INTEGER, REAL, TEXT, BLOB) and no date type. Its date functions accept three spellings of time:

• TEXT: ISO 8601 such as 2026-03-10 09:00:00 or 2026-03-10T09:00:00Z.
• INTEGER: Unix seconds (since 1970-01-01 00:00:00 UTC), read with the 'unixepoch' modifier.
• REAL: a Julian day number, days since noon on 24 November 4714 BC (proleptic Gregorian). 2026-03-01 00:00:00 is 2461100.5.

SELECT typeof('2026-03-10'), typeof(1773057600), typeof(julianday('2026-03-10'));
-- text|integer|real

Before measuring anything, one distinction decides most designs. An instant is something that happened at one point on the global timeline (an order was paid). A wall time is what a clock on a wall shows somewhere (the store opens at 09:00 in Madrid). Instants can all be stored in UTC. Future wall times cannot, because the offset that will apply on that date is not known yet; we come back to this in lecture 6.

Measured: 1,000,000 rows as TEXT, INTEGER and REAL

The benchmark: 1,000,000 random instants between 2020 and 2029 (random.Random(42)), each stored in its own SQLite file as TEXT YYYY-MM-DD HH:MM:SS (UTC), INTEGER Unix seconds, or REAL Julian day. Then one index on the column, a count of one month (ts >= ? AND ts < ?) with and without the index, and a GROUP BY day over all rows.

# bench.py (core), Python 3.9.6, sqlite 3.51.0
kinds = {
    "text":    ("TEXT",    lambda e: datetime.fromtimestamp(e, timezone.utc).strftime("%Y-%m-%d %H:%M:%S")),
    "integer": ("INTEGER", lambda e: e),
    "real":    ("REAL",    lambda e: e / 86400.0 + 2440587.5),   # Julian day number
}
day_expr = {"text": "substr(ts,1,10)", "integer": "date(ts,'unixepoch')", "real": "date(ts)"}
# ... CREATE TABLE ev(id INTEGER PRIMARY KEY, ts <type> NOT NULL); executemany; CREATE INDEX ev_ts ON ev(ts)

Output (min / median in ms; 20 runs for the indexed query, 5 without, 3 for the group-by):

text     insert  2.62s  file  28.1 MB ->  56.2 MB with index | month count idx 0.16/0.17 ms (8503)  no idx 51.3/52.0 ms | group by day 428/431 ms (3653 days)
   plan: SEARCH ev USING COVERING INDEX ev_ts (ts>? AND ts<?)
integer  insert  0.50s  file  13.1 MB ->  26.1 MB with index | month count idx 0.15/0.15 ms (8503)  no idx 37.5/37.7 ms | group by day 423/424 ms (3653 days)
   plan: SEARCH ev USING COVERING INDEX ev_ts (ts>? AND ts<?)
real     insert  0.55s  file  17.1 MB ->  34.2 MB with index | month count idx 0.17/0.17 ms (8503)  no idx 43.8/44.0 ms | group by day 440/447 ms (3653 days)

What the numbers say:

Three surprises. First, the index makes the type almost irrelevant for range queries: 0.15 to 0.17 ms for all three, about 300 times faster than the scan. Second, the text insert of 2.62 s was not SQLite's fault. With the values formatted beforehand, the inserts took 0.48 s (text), 0.44 s (integer) and 0.44 s (real); the other two seconds were Python's strftime. Third, REAL lost nothing at one-second precision: joining the REAL and INTEGER files on id, datetime(r.ts) <> datetime(n.ts,'unixepoch') matched 0 rows, strftime('%f') matched 0 rows, and unixepoch(r.ts) <> n.ts matched 0 rows. The real cost of TEXT is size: more than twice the INTEGER file, both in the table and again in the index.

datetime('now') is UTC, whatever the server says

A classic report from a server in Japan: "every time in the database is nine hours off." Nothing is off. SQLite's 'now' is always UTC; only the 'localtime' modifier reads the process zone. Same command, four zones (the 'now' column is the moment we ran it, in UTC):

for tz in UTC Asia/Tokyo America/New_York Europe/Madrid; do
  TZ=$tz sqlite3 :memory: "SELECT datetime('now'), datetime(1773057600,'unixepoch'),
                                  datetime(1773057600,'unixepoch','localtime');"
done

UTC                2026-11-15 10:03:42|2026-03-09 12:00:00|2026-03-09 12:00:00
Asia/Tokyo         2026-11-15 10:03:42|2026-03-09 12:00:00|2026-03-09 21:00:00
America/New_York   2026-11-15 10:03:42|2026-03-09 12:00:00|2026-03-09 08:00:00
Europe/Madrid      2026-11-15 10:03:42|2026-03-09 12:00:00|2026-03-09 13:00:00

Note New York: 08:00, not 07:00, because US daylight saving time began on 8 March 2026 (lecture 2). The 'localtime' modifier knew that, because it uses the tz database. Two rules follow: keep datetime('now') and convert at display time, and never put 'localtime' into stored data. SQLite enforces part of this itself. An expression index on datetime(at,'localtime') fails as soon as the index has to hold a row, whether the row was inserted before or after CREATE INDEX:

Error: stepping, non-deterministic use of datetime() in an index

An index on unixepoch(at) is fine: EXPLAIN QUERY PLAN showed SEARCH ev USING COVERING INDEX ev_at_epoch (<expr>>? AND <expr><?).

Common mistake: ISO text with mixed offsets

ISO 8601 text sorts correctly only if every value is in the same format and the same offset. Teams break this when each client sends its own local time and the server stores the string unchanged:

CREATE TABLE b(id INTEGER PRIMARY KEY, at TEXT);
INSERT INTO b(at) VALUES
 ('2026-03-10T09:00:00+09:00'),   -- Tokyo, 00:00 UTC
 ('2026-03-09T20:00:00-05:00'),   -- New York, 01:00 UTC on the 10th
 ('2026-03-10T01:30:00+01:00'),   -- Madrid, 00:30 UTC
 ('2026-03-09T23:45:00Z');        -- UTC
SELECT id, at FROM b ORDER BY at;
SELECT id, at, datetime(at), unixepoch(at) FROM b ORDER BY unixepoch(at);

text order:
2|2026-03-09T20:00:00-05:00
4|2026-03-09T23:45:00Z
3|2026-03-10T01:30:00+01:00
1|2026-03-10T09:00:00+09:00
instant order:
4|2026-03-09T23:45:00Z|2026-03-09 23:45:00|1773099900
1|2026-03-10T09:00:00+09:00|2026-03-10 00:00:00|1773100800
3|2026-03-10T01:30:00+01:00|2026-03-10 00:30:00|1773102600
2|2026-03-09T20:00:00-05:00|2026-03-10 01:00:00|1773104400

Text order is 2, 4, 3, 1. True order is 4, 1, 3, 2. The New York row, which happened last, sorts first. At scale it is worse: 10,000 events over March 2026 with random offsets from four zones (seed 42):

rows at the same position in both orders: 93
adjacent pairs out of order in text order: 3723 of 9999
'March 10 UTC' as text range: 331 rows, true: 335, overlap: 283

The text range query for "March 10 UTC" returned 331 rows, of which only 283 were correct: 48 rows from other days leaked in and 52 real rows were missing. The totals look plausible, which is why nobody notices.

Fix: convert to UTC before the INSERT (datetime(?) normalizes any offset to UTC text, or store unixepoch(?)), and if legacy data already has mixed offsets, sort and filter by unixepoch(at) with an expression index on it.

Two more text traps from the same session:

SELECT '2026-03-10 09:00:00' < '2026-03-10T08:00:00';   -- 1  (space sorts before 'T')
SELECT quote(datetime('2026-02-30 10:00:00'));          -- '2026-03-02 10:00:00'
SELECT quote(date('2026-02-29'));                       -- '2026-03-01'  (2026 is not a leap year)
SELECT quote(datetime('2026-13-01')), quote(datetime('2026/03/10 10:00'));  -- NULL|NULL

The first is the trap of mixing your own T strings with SQLite's space-separated output from datetime(): 09:00 compares as "earlier" than 08:00. The second is quiet normalization: SQLite turns 30 February into 2 March instead of failing, the same overflow you met in PHP in lecture 3. The third: unknown formats return NULL, so a NOT NULL constraint is a cheap guard.

Your driver also has opinions: Python sqlite3

Python's sqlite3 module can convert datetime objects for you. With detect_types=PARSE_DECLTYPES and a column declared TIMESTAMP, a naive and an aware value went in fine, but reading them back failed on the aware one:

3.9.6
[('2026-03-10 09:00:00',), ('2026-03-10 09:00:00+09:00',)]
ValueError: invalid literal for int() with base 10: b'00+09'

On Python 3.12 the same script first prints:

~/demo/adapt.py:8: DeprecationWarning: The default datetime adapter is deprecated as of Python 3.12; see the sqlite3 documentation for suggested replacement recipes
~/demo/adapt.py:12: DeprecationWarning: The default timestamp converter is deprecated as of Python 3.12; see the sqlite3 documentation for suggested replacement recipes

and then fails with the same ValueError. Do the conversion yourself: int(dt.timestamp()) on the way in, datetime.fromtimestamp(x, timezone.utc) on the way out. Your code then does the same thing on every Python version.

MySQL DATETIME vs TIMESTAMP, and other databases (not run here)

We did not run a MySQL or PostgreSQL server for this lecture; the following is the documented behaviour.

The MySQL TIMESTAMP upper bound is exactly the 32-bit limit in the next section. A shared host whose time_zone you cannot set, plus TIMESTAMP columns, is a common source of "times moved after migration". Setting the session time zone to UTC (SET time_zone = '+00:00') in the connection setup makes the conversion a no-op. For other MySQL limits on shared hosting see max_connections_per_hour on shared hosting.

Y2K38: a 32-bit counter in C, measured

Unix seconds in a signed 32-bit integer end at 2,147,483,647, which is 2038-01-19 03:14:07 UTC. On this Mac time_t is 64-bit, so we force the 32-bit case with int32_t:

/* y2k38.c */
#include <inttypes.h>
#include <stdint.h>
#include <stdio.h>
#include <time.h>

static void show(const char *label, time_t t) {
    char buf[32];
    struct tm tm;
    gmtime_r(&t, &tm);
    strftime(buf, sizeof buf, "%Y-%m-%d %H:%M:%S", &tm);
    printf("%-22s %12lld  %s UTC\n", label, (long long)t, buf);
}

int main(void) {
    printf("sizeof(time_t) = %zu bytes\n", sizeof(time_t));
    int32_t last = INT32_MAX;                         /* 2147483647 */
    show("int32 max", last);
    show("time_t max+1 (64-bit)", (time_t)last + 1);
    int32_t wrapped = (int32_t)((uint32_t)last + 1u); /* what a 32-bit counter does */
    show("int32 after +1 (wrap)", wrapped);
    int32_t ub = last;
    ub = ub + 1;                                      /* signed overflow: undefined behaviour */
    show("int32 + 1 (UB)", ub);
    return 0;
}

$ clang -std=c17 -Wall -Wextra -O0 y2k38.c -o y2k38 && ./y2k38
sizeof(time_t) = 8 bytes
int32 max                2147483647  2038-01-19 03:14:07 UTC
time_t max+1 (64-bit)    2147483648  2038-01-19 03:14:08 UTC
int32 after +1 (wrap)   -2147483648  1901-12-13 20:45:52 UTC
int32 + 1 (UB)          -2147483648  1901-12-13 20:45:52 UTC

One second after 2038-01-19 03:14:07, a 32-bit counter jumps back to 1901-12-13 20:45:52. The compiler gave no warning for ub + 1; only UBSan (-fsanitize=undefined) caught it at run time:

y2k38.c:22:13: runtime error: signed integer overflow: 2147483647 + 1 cannot be represented in type 'int'

Why it is "undefined" and not just "wraps" is the subject of undefined behaviour and optimization. Where 32-bit time still hides in 2026: binary file formats and network protocols with 4-byte fields, MySQL TIMESTAMP, old embedded firmware, and code that packs time into a 32-bit int. Python's struct refuses the value outright: struct.error: 'i' format requires -2147483648 <= number <= 2147483647. SQLite has no such limit: datetime(2147483648,'unixepoch') printed 2038-01-19 03:14:08.

JSON APIs: send ISO 8601 with an offset, receive with suspicion

JSON has no date type, so every API picks a convention. The one that survives zone changes is ISO 8601 / RFC 3339 with an explicit offset, ideally UTC with Z. What the tools did:

# Python 3.9.6
TypeError: Object of type datetime is not JSON serializable
{"at": "2026-03-10T09:00:00+09:00"} {"at": "2026-03-10T00:00:00Z"}
ValueError: Invalid isoformat string: '2026-03-10T00:00:00Z'      # fromisoformat on 3.9
2026-03-10 00:00:00+00:00                                          # same call on 3.12

json.dumps refuses datetime; you choose the format, which is good. Python 3.9's fromisoformat cannot parse the Z suffix that JavaScript produces; Python 3.11 added it (we saw it work on 3.12). JavaScript under two explicit zones:

const d = new Date("2026-03-10T09:00:00+09:00");
print(JSON.stringify({ at: d }));
print(JSON.stringify({ at: new Date("2026-03-10T09:00:00") }));   // no offset!
print(JSON.stringify({ at: new Date("not a date") }));

TZ=UTC
{"at":"2026-03-10T00:00:00.000Z"}
{"at":"2026-03-10T09:00:00.000Z"}
{"at":null}
TZ=Asia/Tokyo
{"at":"2026-03-10T00:00:00.000Z"}
{"at":"2026-03-10T00:00:00.000Z"}
{"at":null}

The string with an offset meant the same instant everywhere. The string without an offset was read as local time, so the same code sent two different instants depending on the server's zone. And an invalid date became null without an exception, so validate before you serialize. HTTP headers follow yet another convention (Date: ... GMT), covered in HTTP caching.

Rules for an API contract:

• Instants: "2026-03-10T00:00:00Z", or epoch seconds in a field whose name says so (created_at_unix). Never epoch numbers without a unit; lecture 1 showed what ms read as s does.
• Dates without time (birthdays, due dates): "2026-03-10", no zone, no midnight.
• Future local events: the local time plus an IANA zone name, {"local": "2026-03-10T09:00", "zone": "America/New_York"}.
• Reject strings without an offset at the API boundary instead of guessing.

Honest limits and version notes

• Measured only on SQLite 3.51.0 on an Apple Silicon Mac (M1 Pro), one run of the benchmark script; your absolute numbers will differ, the ratios (2x size, equal index speed) should not. unixepoch() was added in SQLite 3.38.0 and the 'subsec' modifier in 3.42.0; on an older SQLite use strftime('%s', ...).
• MySQL and PostgreSQL behaviour is described from their documentation, not run here.
• The Python sqlite3 default adapters are deprecated since Python 3.12 (warning shown above). datetime.fromisoformat accepts Z since Python 3.11.
• time_t is 64-bit on this macOS; the 32-bit case was forced with int32_t. 32-bit Linux userlands moved to 64-bit time_t at different times; we did not run any.
• REAL Julian days round-tripped exactly at one-second precision for our 1M rows. We did not test microsecond data; if you need microseconds, INTEGER microseconds is the safe choice.

Exercise

1. A table orders(created TEXT) holds values like 2026-03-10T09:00:00+09:00 and 2026-03-09 20:00:00 (no offset, written by an old app server that ran in New York). Write the first two things you do before touching any query.

Answers

1. First, find out what each format means: count rows per pattern (length(created), substr(created, 20)) and confirm, from logs or code, which zone wrote the offset-less rows. Second, add a new INTEGER column and backfill it: unixepoch(created) for rows with an offset, and for the New York rows convert in application code with ZoneInfo("America/New_York") (SQLite cannot apply a named zone other than the process one). Only then switch reads to the new column and add an index on it.

Previous: Dates & Time Zones 4: date arithmetic, months, leap years and durations · Next: Dates & Time Zones 6: scheduling, cron, time zones and clock skew · Back to the programming learning roadmap.