SQL, data modeling, data quality, EDA, visualization · insight-1047
In SQLite, treat dates as text with a fixed format and never mix representations in one column
- Evidence type
- documented fact
- Difficulty
- easy
- Roles
- Data analyst, Data scientist
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
You query the airline sample database, where the date column is TEXT, and a join or filter mixes ISO strings with strings such as 3/4/2023 or with Unix timestamps from another source.
Action
Normalise to one ISO 8601 text format (or one integer epoch) at ingestion, use SQLite's date functions for arithmetic, and add a check that every value matches the expected pattern. Cast explicitly in comparisons rather than trusting column affinity.
Reason
SQLite has no date storage class; dates are TEXT, REAL or INTEGER, and comparison follows affinity and storage-class rules.
Boundary
SQLite files, CSV imports into any loosely typed store and pandas-to-SQLite round trips.
Symptom or risk
Ranges and sorts compare strings or numbers by storage class, so "2023-03-04" sorts correctly while "3/4/2023" sorts after "12/..." and an INTEGER or REAL value, when it is stored as one, compares below any text.
Exceptions
Engines with real date types, where the database rejects malformed values.
Trade-offs
ISO text sorts correctly but uses more space than an integer epoch.
Pitfalls
Local timestamps without offsets written next to UTC ones in the same column.
Example
Synthetic airline data: flight_day.date is TEXT in YYYY-MM-DD, so BETWEEN '2023-03-04' AND '2023-03-12' works only because every value shares that format.
Lessons and concepts
Claims and their sources
SQLite has no dedicated date and time storage class; dates can be stored as TEXT (ISO 8601), REAL (Julian day) or INTEGER (Unix time), and values compare according to affinity and storage class.