Date/Time Types and STRICT Tables

The data we'll use — perf_sales

Dates are stored as either strings or numbers, and you read or compute them with functions like date() / strftime().

In the first half, you'll use the sale_date column of the perf_sales sales table to check the three storage formats and how to extract values; in the second half, you'll create a STRICT table yourself to see what happens when you insert a value that doesn't match the declared type.

Before the exercises, take a look at the column definitions and a sample of the data of the perf_sales table used in this article. (When you run it correctly, the explanation will appear.)

① Run PRAGMA table_info(perf_sales); to check the column definitions.

② Run SELECT * FROM perf_sales LIMIT 5; to preview the first 5 rows. perf_sales has many rows, so always add LIMIT.

The three date storage formats — ISO string / julianday / unixepoch

Dates and times can be stored in three main formats.

Whichever format you store, you can read it back as 'YYYY-MM-DD' with date() or as 'YYYY-MM-DD HH:MM:SS' with datetime().

This course uses the ISO string by default because it's human-readable and easy to compare, and only converts to julianday / unixepoch when needed.

-- The same date in 3 formats (read-only example)
SELECT '2024-03-15'                       AS iso_text,
       julianday('2024-03-15')            AS as_julianday,
       strftime('%s','2024-03-15 00:00:00') AS as_unixepoch;

-- Convert each of the 3 formats back to a readable date
SELECT date(julianday('2024-03-15'))            AS from_jd,
       datetime(1710460800,'unixepoch')         AS from_unix,
       date('2024-03-15')                       AS from_iso;

-- With ISO strings, range comparison works correctly as plain string comparison
SELECT '2024-03-15' BETWEEN '2024-01-01' AND '2024-12-31' AS in_2024;  -- 1

Suppose the requirement is: "For one sale in perf_sales, show its sale date side by side in the ISO string, julianday, and unixepoch formats."

① Narrow perf_sales down to the single row where sale_id = 12345 (since the table is large, always limit to one row).

② Read that row's sale_date as-is under the alias iso.

③ Alongside it, output julianday() applied to the same sale_date as jd, and strftime('%s', ...) as unixep.

Manipulate and aggregate dates with date() and strftime()

• date(value, modifier...): returns the date part as 'YYYY-MM-DD', and lets you do date math with modifiers like '+1 month' / '-7 days' / 'start of month'
• strftime(format, value): extracts a piece of the date as a string using format specifiers like %Y (year), %m (month), %d (day), %w (day of week)
• Combine strftime('%Y', sale_date) with GROUP BY to aggregate by year
• Because sale_date is stored as an ISO string, range conditions like `WHERE sale_date >= '2024-01-01'` work correctly as plain string comparisons

-- Date manipulation (read-only example)
SELECT date('2024-03-15','+1 month')      AS plus_1_month,   -- 2024-04-15
       date('2024-03-15','start of month') AS month_start,    -- 2024-03-01
       strftime('%Y',   '2024-03-15')      AS year_part,      -- 2024
       strftime('%Y-%m','2024-03-15')      AS year_month;     -- 2024-03

-- Count by year (ISO string, so extract the year with strftime and GROUP BY)
SELECT strftime('%Y', sale_date) AS yr, COUNT(*) AS cnt
FROM perf_sales
GROUP BY yr
ORDER BY yr;

Suppose the requirement is: "Group the sales in perf_sales by year and show the count and total amount for each year." Since sale_date is an ISO string, use strftime to extract the year and then aggregate.

① Extract the year from sale_date with strftime('%Y', ...) and alias it as yr.

② Output the count of each year as cnt, and the sum of amount as total.

③ Group by year (yr) and order the results in ascending year order (always aggregate — the table has many rows).

Suppose the requirement is: "For Q1 2024 (January 1 to March 31) only, show the count and average amount." Because sale_date is an ISO string, range conditions work correctly as plain string comparisons.

① From perf_sales, keep only rows where sale_date is between 2024-01-01 and 2024-03-31 (inclusive).

② Output the count of those rows as cnt and the average of amount as avg_amount.

③ Don't group — return a single aggregate row covering the whole period.

STRICT tables — enforce strict typing

The type affinity we've seen so far flexibly converts numeric-looking strings.

By contrast, when you add STRICT at the end of a table definition, that table rejects storing values whose type doesn't match the declared one.

If you try to insert a non-numeric string into a numeric column, the value isn't converted implicitly — you get an error.

Use this for tables where you want to catch type mix-ups early.

In a STRICT table, the allowed column types are limited to INTEGER / REAL / TEXT / BLOB / ANY — INT and custom type names aren't allowed.

In the next exercise you'll create a typed_strict table with STRICT yourself, and in the following exercises you'll insert both matching and mismatched values to see the difference.

-- Declaring and using a STRICT table (read-only example)
CREATE TABLE IF NOT EXISTS typed_strict(a INTEGER, b TEXT, c REAL) STRICT;

-- Rows with matching types insert just fine
INSERT INTO typed_strict VALUES (1, '2024-03-15', 1.5);

-- Inserting a non-numeric string into the a INTEGER column
-- is not implicitly converted -- the INSERT errors out:
--   cannot store TEXT value in INTEGER column typed_strict.a
INSERT INTO typed_strict VALUES ('not-a-number', 'x', 1.0);

Suppose the requirement is: "Define a table with STRICT myself so I can check the type-checking behavior in the following exercises." Before the next two exercises (where you'll insert matching and mismatched rows), create the table itself here.

① First run DROP TABLE IF EXISTS typed_strict; to clear any previous version, then CREATE a typed_strict table with three columns — a (INTEGER) / b (TEXT) / c (REAL) — adding STRICT at the end.

② Then run PRAGMA table_info(typed_strict); to confirm the three columns are defined as declared.

Suppose the requirement is: "Add one row to the typed_strict table from Exercise 4 with values that match each column's declared type, and confirm it's stored."

① Insert one row into typed_strict (a INTEGER / b TEXT / c REAL): an integer in a, an ISO-format date string in b, and a decimal in c. You can pick the values freely (use a date-like string in b for example).

② Then run SELECT * FROM typed_strict; to confirm the inserted row is there.

Let's check what happens when you try to insert a non-numeric string into the a column (INTEGER) of typed_strict. A STRICT table doesn't convert mismatched values — it rejects them, so this INSERT will error. This is the final exercise of the article, and the point is to experience the error itself.

① Run an INSERT that tries to put a non-numeric string like 'not-a-number' into the a column of typed_strict.

② Confirm that an error message appears (in this console, getting an error means you got it right).