SQL・データベース
PR

SQLの日付と時刻の関数|date・strftime・修飾子・日数の差を実行結果で解説

SQLの日付と時刻の関数|date・strftime・修飾子・日数の差を実行結果で解説
hr
記事内に商品プロモーションを含む場合があります

「30 日以内の注文だけ出したい」「月ごとの売上を集計したい」「保存した日時が 9 時間ずれている」——日付と時刻の扱いは、SQL を書き始めるとすぐにぶつかるテーマです。SQLite(エスキューライト)では、date・datetime・strftime などの関数と、'+1 day' のような「修飾子」の組み合わせで、ほとんどの日付の計算ができます。

この記事では、練習用の注文テーブルで実際に実行した結果を見ながら、関数の使い方、‘now’ が日本時間にならない理由と直し方、月末や日数の差の計算、月ごとの集計、NULL になったり並び順が狂ったりする日付の書き方を解説します。GROUP BY の基本は「SQLのGROUP BYと集計関数」、UTC と日本時間の考え方は「タイムゾーンとUTC・ISO 8601の基本」で解説しています。

SQLite には日付型がない:3 つの持ち方

MySQL や PostgreSQL には DATE・DATETIME などの日付専用の型がありますが、SQLite にはありません。SQLite の公式ドキュメント(Datatypes In SQLite)には「日付や時刻のための専用のストレージクラスはない。組み込みの日付と時刻の関数は、日付と時刻を TEXT・REAL・INTEGER の値として扱える」とあります。

型中身2026-10-23 0 時(UTC)の例作る関数
TEXTISO 8601 形式の文字列2026-10-23 00:00:00date・time・datetime・strftime
REALユリウス日(紀元前 4714 年 11 月 24 日の正午からの日数)2461336.5julianday
INTEGERUnix 時間(1970-01-01 00:00:00 UTC からの秒数)1792713600unixepoch

関数を使えば互いに変換できます。迷ったら、目で読めて並べ替えもそのまま正しくできる TEXT(YYYY-MM-DD HH:MM:SS)がおすすめです。この記事の練習データも TEXT で持っています。

練習用のテーブルを用意する

Python に標準で入っている SQLite を使います。次のコードを sql_date_practice.py という名前で保存してください。注文(orders)テーブルをメモリー上に作ります。注文日時(ordered_at)は UTC(協定世界時)で保存しています。

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE orders (
  id         INTEGER PRIMARY KEY,
  item       TEXT    NOT NULL,
  price      INTEGER NOT NULL,
  ordered_at TEXT    NOT NULL
);
INSERT INTO orders VALUES
  ( 1, 'マウス',         2980,  '2026-09-03 01:15:00'),
  ( 2, 'ノートPC A',     98000, '2026-09-10 05:40:00'),
  ( 3, 'キーボード',     6480,  '2026-09-18 11:20:00'),
  ( 4, 'マウス',         2980,  '2026-09-30 16:30:00'),
  ( 5, 'USBメモリ 32GB', 1280,  '2026-10-01 00:10:00'),
  ( 6, '外付けSSD 1TB',  12800, '2026-10-02 03:05:00'),
  ( 7, 'モニター 24型',  21800, '2026-10-05 14:50:00'),
  ( 8, 'マウス',         2980,  '2026-10-08 22:30:00'),
  ( 9, 'ノートPC B',     128000,'2026-10-10 02:00:00'),
  (10, 'USBメモリ 32GB', 1280,  '2026-10-31 15:20:00');
""")

print("SQL を入力して Enter(終了は exit)")
while True:
    sql = input("sql> ").strip()
    if sql.lower() in ("exit", "quit"):
        break
    if not sql:
        continue
    try:
        cur = con.execute(sql)
    except sqlite3.Error as e:
        print("エラー:", e)
        continue
    if cur.description is None:
        print("(結果の表はありません)")
        continue
    print(" | ".join(d[0] for d in cur.description))
    rows = cur.fetchall()
    for row in rows:
        print(" | ".join("NULL" if v is None else str(v) for v in row))
    print(f"({len(rows)} 行)")

保存したフォルダーで次のコマンドを実行すると「sql>」と表示されるので、この記事の SQL を 1 行で入力して Enter を押します(Windows 11 の Python 3.12・SQLite 3.45.3 で確認しました)。

py sql_date_practice.py

SQLite のバージョンは SELECT sqlite_version(); で確かめられます。unixepoch 関数は 3.38.0 以降、timediff 関数は 3.43.0 以降で使えます(SQLite の公式の変更履歴で確認)。

日付と時刻の関数の基本

SQLite の公式ドキュメント(Date And Time Functions)には次の 7 つの関数が載っています。最初の引数に日時、その後ろに修飾子をいくつでも書けます。

関数返すもの2026-10-23 15:30:00 を渡した結果
date(日時, 修飾子…)日付 YYYY-MM-DD2026-10-23
time(日時, 修飾子…)時刻 HH:MM:SS15:30:00
datetime(日時, 修飾子…)日時 YYYY-MM-DD HH:MM:SS2026-10-23 15:30:00
julianday(日時, 修飾子…)ユリウス日(小数)2461337.1458333335
unixepoch(日時, 修飾子…)Unix 時間(整数)1792769400
strftime(書式, 日時, 修飾子…)書式どおりの文字列strftime(‘%Y/%m/%d’, …) → 2026/10/23
timediff(日時A, 日時B)B から A までの差を表す文字列(後の章で説明)
SELECT id, ordered_at, date(ordered_at) AS 日付, time(ordered_at) AS 時刻, unixepoch(ordered_at) AS unix FROM orders WHERE id <= 2;
id | ordered_at | 日付 | 時刻 | unix
1 | 2026-09-03 01:15:00 | 2026-09-03 | 01:15:00 | 1788398100
2 | 2026-09-10 05:40:00 | 2026-09-10 | 05:40:00 | 1789018800
(2 行)

数値から日時に戻すときは、ユリウス日はそのまま、Unix 時間は 'unixepoch' 修飾子を付けて渡します。datetime(1788398100, 'unixepoch') は 2026-09-03 01:15:00 になりました。Unix 時間が何月何日なのかをさっと確かめたいときは、Unix 時間変換ツールに貼り付けると、日本時間と UTC の両方で表示できます。

関数に渡せるのは YYYY-MM-DD、YYYY-MM-DD HH:MM:SS(間は「T」も可)、'now'、数値などの決まった形式です。+09:00 のようなオフセットも付けられ、datetime('2026-10-23T15:30:00+09:00') は UTC の 2026-10-23 06:30:00 になりました。

strftime で好きな形式にする

strftime は、1 つ目の引数の書式に従って日時を文字列にします。よく使う書式は次のとおりです(公式ドキュメントの一覧から抜粋)。

書式意味2026-10-23 09:05:00 の結果
%Y / %m / %d年(4 桁)/ 月 / 日(2 桁)2026 / 10 / 23
%H / %M / %S時 / 分 / 秒(2 桁)09 / 05 / 00
%w曜日(日曜が 0、土曜が 6)5(金曜)
%jその年の何日目か296
%sUnix 時間1792746300

公式ドキュメントには「定義されていない書式があると、結果は NULL になる」とあり、実際 strftime('%Q', '2026-10-23') は NULL でした。また結果は文字列なので、strftime('%w', …) と比べるときは = '0' のように文字列で書きます。

‘now’ は UTC:日本時間にするには

現在日時は 'now' で取れます。日本時間の 15 時 39 分に実行した結果がこちらです。

SELECT datetime('now'), datetime('now', 'localtime'), datetime('now', '+9 hours');
datetime('now') | datetime('now', 'localtime') | datetime('now', '+9 hours')
2026-10-23 06:39:03 | 2026-10-23 15:39:03 | 2026-10-23 15:39:03
(1 行)

datetime('now') は 9 時間前の 06:39 です。公式ドキュメントに「’now’ は現在の日時に変換される。UTC(協定世界時)が使われる」とあるとおり、SQLite の ‘now’ は PC の設定に関係なく UTC です。日本時間(JST)は UTC より 9 時間進んでいるので、'localtime' か '+9 hours' の修飾子を付けて変換します。保存してある UTC の日時も同じように変換できます。

SELECT id, ordered_at AS UTC, datetime(ordered_at, '+9 hours') AS 日本時間 FROM orders WHERE id IN (4, 7, 8, 10);
id | UTC | 日本時間
4 | 2026-09-30 16:30:00 | 2026-10-01 01:30:00
7 | 2026-10-05 14:50:00 | 2026-10-05 23:50:00
8 | 2026-10-08 22:30:00 | 2026-10-09 07:30:00
10 | 2026-10-31 15:20:00 | 2026-11-01 00:20:00
(4 行)
左に UTC で保存している日時、右に日本時間を並べた図。now の 2026-10-23 06:39:03 は 15:39:03 に、id 4 の 2026-09-30 16:30:00 は 2026-10-01 01:30:00 に、id 10 の 2026-10-31 15:20:00 は 2026-11-01 00:20:00 になり、localtime または +9 hours の修飾子で 9 時間進めると月が変わることを示している
図 1:UTC の日時と日本時間の対応。日付をまたぐ注文は集計する月が変わる

id 4 は UTC では 9 月 30 日ですが、日本のお客さんにとっては 10 月 1 日の注文です。どちらの日付で集計するかによって、月別・日別の結果が変わります(後の章で実例を示します)。

‘localtime’ と ‘+9 hours’ の違い

'localtime' は「左側の日時を UTC とみなしてローカル時刻に合わせる」修飾子(公式ドキュメント)で、ローカル時刻は SQLite を動かす PC・サーバーのタイムゾーン設定で決まります。確認した PC は「Tokyo Standard Time」なので 9 時間進みましたが、タイムゾーンが UTC のサーバーでは時刻が変わらないと考えられます(サーバーでは未確認)。日本には夏時間がないので、常に日本時間にしたいなら ‘+9 hours’ が確実です。

修飾子で日付を計算する

足し算・引き算:’+1 day’・’-1 month’ など

'+N days'・'+N hours'・'+N minutes'・'+N months'・'+N years' などで足し引きできます。修飾子は左から順に適用されます。公式ドキュメントでは、末尾の s は省略でき、N には小数も書けるとされています。

SQL結果
date(‘2026-10-23’, ‘+10 days’)2026-11-02
date(‘2026-10-23’, ‘-1 month’)2026-09-23
datetime(‘2026-10-23 10:00:00’, ‘+1.5 hours’)2026-10-23 11:30:00
date(‘2026-10-23 20:00:00’, ‘+9 hours’)2026-10-24

月初・月末を求める:’start of month’

'start of month'・'start of year'・'start of day' は、その月・年・日の始まりに戻す修飾子です。月末は「月初に戻す → 1 か月足す → 1 日引く」と組み合わせて求めます。

SQL結果意味
date(‘2026-10-23’, ‘start of month’)2026-10-01月初
date(‘2026-10-23’, ‘start of month’, ‘+1 month’, ‘-1 day’)2026-10-31月末
date(‘2026-10-23’, ‘weekday 1’)2026-10-26次の月曜日(その日が月曜ならその日)

'weekday N' は、N 曜日(日曜が 0)になるまで日付を進めます。なお、2026 年 10 月 9 日の SQLite 3.54.0 で 'end of month' などの修飾子が追加されたと公式ドキュメントにありますが、この PC の 3.45.3 では使えないため確認していません。

月の足し引きで月末を越えるときの落とし穴

3 月 31 日の「1 か月前」は 2 月 28 日だと思いがちですが、SQLite 3.45.3 では違う結果になりました。

SELECT date('2026-03-31', '-1 month'), date('2026-01-31', '+1 month'), date('2026-02-31');
date('2026-03-31', '-1 month') | date('2026-01-31', '+1 month') | date('2026-02-31')
2026-03-03 | 2026-03-03 | 2026-03-03
(1 行)

月だけを動かして「2026-02-31」という存在しない日付になり、2 月は 28 日までなので、あふれた 3 日分が 3 月に繰り越されています(3 つ目の date('2026-02-31') も同じ結果です)。「先月」を求めたいときは、先に ‘start of month’ で 1 日にそろえてから月を動かすと、あふれが起きません。

SELECT date('2026-03-31', 'start of month', '-1 month'), date('2026-01-31', 'start of month', '+2 months', '-1 day');
date('2026-03-31', 'start of month', '-1 month') | date('2026-01-31', 'start of month', '+2 months', '-1 day')
2026-02-01 | 2026-02-28
(1 行)

1 つ目は先月の 1 日、2 つ目は「1 月 31 日の翌月の月末」です。公式ドキュメントによると、3.46.0 以降には解決方法を選ぶ 'ceiling'(既定)と 'floor'(前の月の末日にする)の修飾子があります(3.45.3 では NULL)。MySQL と PostgreSQL は「その月の末日にそろえる」と公式ドキュメントにあり、データベースによって結果が違うところです。

日付の差を求める:julianday の引き算

SQLite には日数の差を返す専用の関数はなく、ユリウス日どうしを引き算して求めます。ユリウス日は「日数」なので、差がそのまま日数になります。時刻を含むと小数になるので、date() で日付だけにしてから引き、CAST で整数にすると分かりやすくなります。

SELECT id, date(ordered_at) AS 注文日, CAST(julianday('2026-10-23') - julianday(date(ordered_at)) AS INTEGER) AS 経過日数 FROM orders WHERE id IN (1, 5, 9);
id | 注文日 | 経過日数
1 | 2026-09-03 | 50
5 | 2026-10-01 | 22
9 | 2026-10-10 | 13
(3 行)

date() を使わずに引くと、id 9 は 12.916666666511446(12 日と 22 時間。末尾のずれは浮動小数点数の誤差)でした。秒の差は unixepoch(A) - unixepoch(B) で求められます。

3.43.0 以降の timediff 関数は、timediff('2026-12-25', '2026-10-23') が +0000-02-02 00:00:00.000(2 か月と 2 日)という人が読むための文字列になるので、計算には julianday の差のほうが扱いやすいでしょう。

実例:月ごと・日ごとに集計する

strftime で「年-月」の文字列を作り、それで GROUP BY すると月別の集計になります。まずは UTC のまま集計します。

SELECT strftime('%Y-%m', ordered_at) AS 月, COUNT(*) AS 件数, SUM(price) AS 売上 FROM orders GROUP BY 月 ORDER BY 月;
月 | 件数 | 売上
2026-09 | 4 | 110440
2026-10 | 6 | 168140
(2 行)

次に、strftime に ‘+9 hours’ を付けて、日本時間で集計します。

SELECT strftime('%Y-%m', ordered_at, '+9 hours') AS 月, COUNT(*) AS 件数, SUM(price) AS 売上 FROM orders GROUP BY 月 ORDER BY 月;
月 | 件数 | 売上
2026-09 | 3 | 107460
2026-10 | 6 | 169840
2026-11 | 1 | 1280
(3 行)

図 1 の id 4(マウス 2,980 円)が 9 月から 10 月へ、id 10(USBメモリ 1,280 円)が 10 月から 11 月へ移りました。日本のお店の「10 月の売上」なら後者が正しい数字です。UTC で保存したデータは、どのタイムゾーンの日付で区切るかを決めてから集計しましょう。日別なら date() を使い、絞り込みにも同じ変換を使います。

SELECT date(ordered_at, '+9 hours') AS 日, COUNT(*) AS 件数, SUM(price) AS 売上 FROM orders WHERE strftime('%Y-%m', ordered_at, '+9 hours') = '2026-10' GROUP BY 日 ORDER BY 日;
日 | 件数 | 売上
2026-10-01 | 2 | 4260
2026-10-02 | 1 | 12800
2026-10-05 | 1 | 21800
2026-10-09 | 1 | 2980
2026-10-10 | 1 | 128000
(5 行)

注文のない日は行が出てきません(GROUP BY は「データがある値」だけを作ります)。曜日ごとなら strftime('%w', ordered_at, '+9 hours') で GROUP BY します。関数を重ねた長い SQL は、SQL 整形ツールで改行を付けると読みやすくなります。

よくある間違い

‘2026/10/1’ や ‘2026-10-1’ を渡すと NULL になる

「スラッシュ区切り」や「ゼロ埋めしない」日付は読めず、エラーにもならずに NULL が返ります。

SELECT d, date(d) FROM (SELECT '2026-10-1' AS d UNION ALL SELECT '2026/10/01' UNION ALL SELECT '2026-10-01');
d | date(d)
2026-10-1 | NULL
2026/10/01 | NULL
2026-10-01 | 2026-10-01
(3 行)

'20261001' も NULL でした。strftime も同じで、月別集計に「NULL」の月ができてしまいます。データを入れる前に YYYY-MM-DD の形に直しておきましょう。Python なら datetime.datetime.strptime("2026/10/1", "%Y/%m/%d").date().isoformat() で 2026-10-01 になりました(「Pythonのdatetimeの使い方」で解説しています)。

ゼロ埋めしない日付は、文字列の比較で順番が狂う

TEXT の日付は、< や ORDER BY で文字列として比べられます。桁がそろっていれば「文字列の順番=日付の順番」になりますが、そろっていないと順番が狂います。

SELECT d FROM (SELECT '2026-10-1' AS d UNION ALL SELECT '2026-9-30' UNION ALL SELECT '2026-10-05' UNION ALL SELECT '2026-10-12') ORDER BY d;
d
2026-10-05
2026-10-1
2026-10-12
2026-9-30
(4 行)

文字列は先頭から 1 文字ずつ比べるので、「2026-10-05」と「2026-10-1」は 9 文字目の「0」と「1」の比較で 10 月 5 日が先になり、6 文字目が「9」の「2026-9-30」は 10 月のどの日よりも後ろになります。SELECT '2026-9-30' < '2026-10-01'; も 0(偽)でした。月と日は必ず 2 桁で保存します。

BETWEEN で月末の日の注文が漏れる

SELECT id, item, ordered_at FROM orders WHERE ordered_at BETWEEN '2026-10-01' AND '2026-10-31';

結果は id 5〜9 の 5 行で、10 月 31 日の id 10 が出てきません。'2026-10-31 15:20:00' は '2026-10-31' より長い分だけ文字列として「大きい」からです。時刻を含む列は、「開始日以上、翌月 1 日より小さい」で書くと漏れません。

SELECT id, item, ordered_at FROM orders WHERE ordered_at >= '2026-10-01' AND ordered_at < '2026-11-01';

こちらは id 5〜10 の 6 行が返りました(UTC で区切った 10 月です)。

「T」区切りと空白区切りが混ざっている

SELECT '2026-10-23T15:30:00' > '2026-10-23 23:00:00'; は 1(真)でした。15 時 30 分のほうが早いのに、「T」の文字コードが空白より大きいためです(datetime() でそろえて比べると 0)。保存する形式は 1 つに決めましょう。

修飾子の書き間違いや Unix 時間の渡し方でも NULL になる

公式ドキュメントに「修飾子が認識できない、形が崩れている、つづりが間違っているときは、関数全体の結果が NULL になる」とあるとおり、date('2026-10-23', '+1 dya') は NULL でした。Unix 時間を date(1792713600) と渡しても NULL です(数値がユリウス日として読まれ、範囲外になるため)。date(1792713600, 'unixepoch') なら 2026-10-23 になります。

Python の datetime をそのまま渡したとき

Python の sqlite3 には、datetime を自動で文字列にする「既定のアダプター」がありますが、Python 3.12 の公式ドキュメントでは「既定のアダプターとコンバーターは 3.12 で非推奨」とされています。次のコード(dt_adapter.py)で試しました。

import datetime
import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE log (at TEXT)")
naive = datetime.datetime(2026, 10, 23, 15, 30)
con.execute("INSERT INTO log VALUES (?)", (naive,))
print(con.execute("SELECT at, typeof(at) FROM log").fetchone())
py dt_adapter.py
dt_adapter.py:7: DeprecationWarning: The default datetime adapter is deprecated as of Python 3.12; see the sqlite3 documentation for suggested replacement recipes
  con.execute("INSERT INTO log VALUES (?)", (naive,))
('2026-10-23 15:30:00', 'text')

保存はされますが警告が出ました(先頭の場所は短くしています)。さらに、この 15:30 が日本時間か UTC か、保存された値からは分かりません。datetime.datetime.now() は PC の時刻(日本時間)なので、そのまま保存すると SQLite の ‘now’(UTC)と 9 時間ずれます。UTC に直した文字列を自分で作って渡すのがおすすめです。datetime.datetime.now(datetime.timezone.utc).strftime("%Y-%m-%d %H:%M:%S") は日本時間の 15 時 32 分に 2026-10-23 06:32:07 を返し、SQLite の datetime('now') と同じ形になりました。これを ? のプレースホルダで渡せば警告は出ません。アダプターとコンバーターの登録方法は「Pythonのsqlite3入門」で解説しています。

MySQL・PostgreSQL での書き方の対応表

MySQL 8.4 と PostgreSQL 18 の公式ドキュメントで確認した、代表的な書き方です。MySQL と PostgreSQL では実行して確かめていないので、お使いのバージョンのマニュアルでも確認してください。

やりたいことSQLiteMySQLPostgreSQL
現在日時datetime(‘now’)(UTC)NOW()(セッションのタイムゾーン)now()(タイムゾーン付き)
書式を指定して文字列にstrftime(‘%Y-%m-%d %H:%M’, 日時)DATE_FORMAT(日時, ‘%Y-%m-%d %H:%i’)to_char(日時, ‘YYYY-MM-DD HH24:MI’)
1 か月後date(日付, ‘+1 month’)DATE_ADD(日付, INTERVAL 1 MONTH)日付 + interval ‘1 month’
月初にそろえるdate(日付, ‘start of month’)—date_trunc(‘month’, 日時)
月末date(日付, ‘start of month’, ‘+1 month’, ‘-1 day’)LAST_DAY(日付)—
日数の差julianday(A) – julianday(B)DATEDIFF(A, B)date 型どうしの A – B
Unix 時間にするunixepoch(日時)UNIX_TIMESTAMP(日時)extract(epoch from 日時)
Unix 時間から日時にdatetime(数値, ‘unixepoch’)FROM_UNIXTIME(数値)to_timestamp(数値)

「—」は、確認した範囲で専用の関数が見当たらず、ほかの関数を組み合わせて書くものです。書式の文字も違い、分は SQLite が %M、MySQL が %i、PostgreSQL が MI です。MySQL の NOW() はセッションのタイムゾーンの時刻で、SQLite のように常に UTC ではありません。

まとめ

  • SQLite に日付型はなく、TEXT(ISO 8601)・REAL(ユリウス日)・INTEGER(Unix 時間)で持つ
  • ‘now’ は UTC。日本時間にするには ‘localtime’(PC の設定による)か ‘+9 hours’ を付ける
  • 月の足し引きは月末であふれるので、先に ‘start of month’ でそろえる
  • 日数の差は julianday の引き算、月別集計は strftime(‘%Y-%m’, …) で GROUP BY
  • ‘2026/10/1’ や ‘2026-10-1’ は NULL になり、文字列の比較も狂う。YYYY-MM-DD HH:MM:SS にそろえる

関連する記事・ツール

ABOUT ME
hr
hr
エンジニアとして働いている hr です。仕事や資格勉強で「こんなツールがあれば」と感じたものを、無料Webツールと解説記事にして発信しています。
記事URLをコピーしました