日付時刻型と STRICT テーブル

本記事で使うデータ — perf_sales

日付は文字列・数値のいずれかに保存し、date() / strftime()などの関数で取り出しや計算をします。

前半では売上テーブルperf_salesのsale_date列で日付の 3 つの保存形式と取り出しを確認し、後半では型を厳格化するSTRICTテーブルを自分で作って、型に合わない値を入れたときの挙動を確認します。

演習に入る前に、本記事で使うperf_salesテーブルの列定義とデータのサンプルを確認しておきます。（正しく実行できれば解説が表示されます）

① PRAGMA table_info(perf_sales);でテーブルの列定義を確認してください。

② SELECT * FROM perf_sales LIMIT 5;で先頭 5 行をプレビューしてください。perf_salesは行数が多いので必ずLIMITを付けてください。

日付の 3 つの保存形式 — ISO 文字列 / julianday / unixepoch

日付時刻は主に 3 つの形式で保存できます。

どの形式で保存しても、date()で'YYYY-MM-DD'に、datetime()で'YYYY-MM-DD HH:MM:SS'に変換して取り出せます。

本講座では人が読めて比較しやすいISO 文字列を基本に使い、必要なときだけ julianday / unixepoch に変換します。

-- 同じ日付を 3 形式で表す（読むだけの別例）
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;

-- 3 形式から読みやすい日付に戻す
SELECT date(julianday('2024-03-15'))            AS from_jd,
       datetime(1710460800,'unixepoch')         AS from_unix,
       date('2024-03-15')                       AS from_iso;

-- ISO 文字列は文字列の大小で範囲比較がそのまま正しい
SELECT '2024-03-15' BETWEEN '2024-01-01' AND '2024-12-31' AS in_2024;  -- 1

「perf_salesのある 1 件の売上日を、ISO 文字列・julianday・unixepoch の 3 形式で並べて確認したい」という要件を想定します。

① perf_salesからsale_id = 12345の 1 行に絞ってください（行数が多いので必ず 1 件に絞ります）。

② その行のsale_dateをそのままisoという別名で取り出してください。

③ 同じsale_dateにjulianday()を適用した値をjd、strftime('%s', ...)を適用した値をunixepという別名で併記してください。

date() と strftime() で日付を加工・集計する

• date(値, 修飾子...): 日付部分を'YYYY-MM-DD'で返し、'+1 month' / '-7 days' / 'start of month'のような修飾子で日付計算ができる
• strftime(書式, 値): %Y（年）・%m（月）・%d（日）・%w（曜日）などの書式指定子で、日付の一部を文字列として取り出す
• strftime('%Y', sale_date)で年だけを取り出してGROUP BYすれば、年別の集計ができる
• sale_dateは ISO 文字列で保存されているので、WHERE sale_date >= '2024-01-01'のような範囲条件がそのまま文字列の大小比較として正しく働く

-- 日付の加工（読むだけの別例）
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

-- 年別に件数を集計（ISO 文字列なので strftime で年を取り出して GROUP BY）
SELECT strftime('%Y', sale_date) AS yr, COUNT(*) AS cnt
FROM perf_sales
GROUP BY yr
ORDER BY yr;

「perf_salesの売上を年ごとにまとめ、年別の件数と金額合計を出したい」という要件を想定します。sale_dateは ISO 文字列なので、strftimeで年を取り出して集計します。

① sale_dateからstrftime('%Y', ...)で年を取り出し、yrという別名にしてください。

② 各年の件数をcnt、amountの合計をtotalという別名で取り出してください。

③ 年（yr）でグループ化し、年の昇順で並べてください（行数が多いので必ず集計してください）。

「2024 年第 1 四半期（1 月 1 日〜3 月 31 日）の売上だけに絞って、件数と平均金額を出したい」という要件を想定します。ISO 文字列のsale_dateは範囲条件がそのまま文字列の大小で正しく働きます。

① perf_salesからsale_dateが2024-01-01以上かつ2024-03-31以下の行に絞ってください。

② 絞り込んだ行の件数をcnt、amountの平均をavg_amountという別名で取り出してください。

③ グループ化はせず、期間全体の 1 行の集計として取得してください。

STRICT テーブル — 型を厳格化する

ここまでの型親和性は、数値らしい文字列を柔軟に変換する扱い方でした。

これに対し、テーブル定義の末尾にSTRICT（厳格）を付けると、その列に宣言した型と異なる値の格納を拒否します。

数値の列に、数値化できない文字列の値を入れようとすると、暗黙変換せずにエラーになります。

型の取り違えを早い段階で検出したいテーブルで使います。

STRICTテーブルでは列に使える型がINTEGER / REAL / TEXT / BLOB / ANYに限られ、INTや独自の型名は使えません。

次の演習ではSTRICT付きのtyped_strictテーブルを自分で作り、その後の演習で型の合う値と合わない値を入れて、挙動の差を体感します。

-- STRICT テーブルの宣言と挙動（読むだけの別例）
CREATE TABLE IF NOT EXISTS typed_strict(a INTEGER, b TEXT, c REAL) STRICT;

-- 型が合う行は問題なく入る
INSERT INTO typed_strict VALUES (1, '2024-03-15', 1.5);

-- a INTEGER 列に数値化できない文字列を入れると
-- 暗黙変換されず、INSERT がエラーになる:
--   cannot store TEXT value in INTEGER column typed_strict.a
INSERT INTO typed_strict VALUES ('not-a-number', 'x', 1.0);

「STRICT を付けたテーブルを自分で定義して、以降の演習で型チェックの挙動を確かめたい」という要件を想定します。次の 2 つの演習で型の合う行と合わない行を入れる前に、まずこの演習でテーブル本体を作っておきます。

① DROP TABLE IF EXISTS typed_strict;で前回分を消してから、a（INTEGER）/ b（TEXT）/ c（REAL）の 3 列を持つtyped_strictテーブルを、末尾にSTRICTを付けてCREATEしてください。

② 続けてPRAGMA table_info(typed_strict);を実行し、3 列が宣言どおりに定義されていることを確認してください。

「実践 4 で作ったtyped_strictテーブルに、各列の宣言型に合った 1 行を追加し、ちゃんと格納されることを確認したい」という要件を想定します。

① typed_strict（a INTEGER / b TEXT / c REAL）に、aに整数、bに ISO 形式の日付文字列、cに小数を 1 行 INSERT してください。値は自由に決めて構いません（例として日付らしい文字列をbに入れてください）。

② 続けてSELECT * FROM typed_strict;を実行し、入れた行が格納されていることを確認してください。

「typed_strictのa列（INTEGER）に、数値化できない文字列を入れようとするとどうなるか」を確認します。STRICT テーブルは型に合わない値を変換せずに拒否するため、この INSERT はエラーになります。本記事の最後の演習なので、エラーの発生そのものを体感する題材です。

① typed_strictのa列に、'not-a-number'のような数値化できない文字列を入れる INSERT を実行してください。

② エラーメッセージが表示されることを確認してください（このコンソールはエラーが出れば正解です）。