How should SQLite store dates and times in a teaching database?

SQLite does not have a dedicated date/time storage class. Its built-in date-and-time functions work with date/time values represented as TEXT, REAL, or INTEGER. [S1]

For a small teaching database, readable TEXT is often the easiest starting point. SQLite’s documentation gives YYYY-MM-DD HH:MM:SS.SSS as an ISO-8601-style example. [S1] A learner can inspect a value and see the date and time rather than decode a number.

The alternatives are useful to explain, too:

1. TEXT: formatted date-and-time text.
2. INTEGER: Unix time in seconds since 1970-01-01 UTC.
3. REAL: a Julian day number. [S1]

These are alternative representations, not three spellings of one dedicated SQLite date type. Choose based on what the lesson needs, then keep that choice consistent for each column.

For a beginner lesson that displays ordinary event times, you could choose TEXT and make the expected format visible in the column name or schema notes. For example:

CREATE TABLE lesson_events (
  event_id INTEGER PRIMARY KEY,
  event_name TEXT NOT NULL,
  starts_at TEXT NOT NULL
);

A sample row could use 2025-03-08 14:30:00.000 in starts_at, following the documented text form. [S1] Treat that as a declared convention for this teaching example, not a special SQLite date type.

If the lesson is specifically about elapsed time or numeric timestamps, an INTEGER column can instead hold Unix time in seconds. State the unit explicitly: an unexplained integer is easy to mistake for a different scale. A REAL column can hold Julian day numbers when that representation is what the lesson is teaching. [S1]

Use one representation and unit per date/time column, and follow it in every row and query. For example, if starts_at is defined by the lesson as formatted TEXT, do not put Unix seconds into some rows. If a column is INTEGER Unix time, document that it means seconds and do not mix in Julian day numbers or a different unit. Those representations can otherwise be difficult to compare or interpret consistently. [S1]

Also remember that SQLite’s type affinity affects how values are stored: TEXT affinity converts numeric input to text, while NUMERIC affinity can convert well-formed numeric text to INTEGER or REAL. [S1] So the declared column type and the inserted values are both part of the lesson’s convention; a type name alone should not be presented as a dedicated date type.

Copy this note into the exercise:

“SQLite has no dedicated date/time storage class. This table stores starts_at as TEXT using YYYY-MM-DD HH:MM:SS.SSS. Use that representation for every value in this column. Do not mix it with Unix seconds or Julian day numbers. If you change the representation, update the example data and the queries that use the column.”

For an integer-based exercise, replace the middle sentence with: “This column stores Unix time in INTEGER seconds since 1970-01-01 UTC.” [S1]

The format choice tells learners how to represent the value; it does not, by itself, establish that every input is valid or settle every question about time-zone conventions, precision, or application-specific validation. The available representations and affinity behavior do not establish those broader guarantees. Keep the lesson’s scope explicit.