MySQL DATE_FORMAT完全ガイド｜安全な使い方と落とし穴

「DATE_FORMAT()、なんとなく使っていませんか？」

MySQLで日付や日時を扱う中で、DATE_FORMAT()を“表示用の便利関数”として何となく使っている人は少なくありません。
しかし実務では、

• WHERE句に使ってしまいインデックスが効かず遅くなる
• 集計キーにしてソート順やJOINで事故る
• 環境差（タイムゾーン・ロケール）で本番だけ結果がズレる

といったトラブルが頻発します。

この記事では、
MySQLのDATE_FORMAT()を「安全に・正しく・長期運用に耐える形」で使い切るために必要な知識をすべて整理します。

• DATE_FORMAT()の役割と他関数との違い
• 指定子の体系的な理解と定番フォーマット
• 集計・検索・インデックスでの落とし穴
• タイムゾーン／ロケール／不正日付への対処
• 実務設計としてのベストプラクティス

初心者でも理解でき、経験者が「そう、それが知りたかった」と納得する深さを目指しています。

DATE_FORMAT()とは何か

「日時を文字列として整形する」ための関数

DATE_FORMAT()は、DATE / DATETIME / TIMESTAMP型の値を、

指定したフォーマットで「文字列として」返す関数です。

DATE_FORMAT(date, format)

重要なのは、

• 値そのものは変えない
• 戻り値は必ず文字列

という点です。

どんな場面で使う関数か

DATE_FORMAT()の主戦場は以下です。

• 管理画面・一覧画面での表示
• CSV / レポート / 帳票出力
• BIツールや外部システムへの受け渡し

つまり、「人や外部が見るための整形」が役割です。

検索・結合・集計の主軸に置く関数ではありません。

基本構文と書き方

基本形

SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM orders;

• 第1引数：日時カラムや日時関数
• 第2引数：フォーマット文字列（指定子＋区切り）

実務ではASで役割を明確にする

SELECT
  created_at,
  DATE_FORMAT(created_at, '%Y-%m-%d') AS created_date
FROM orders;

• 検索・並び替え：created_at
• 表示：created_date

という役割分離が、事故を防ぐ基本設計です。

引数と戻り値の注意点

第1引数（date）

• DATE / DATETIME / TIMESTAMP
• NOW() や CURRENT_TIMESTAMP
• 日時として解釈可能な文字列

※ '2026-01-28 12:34:56' のような標準形式推奨

第2引数（format）

• %から始まる指定子
• - / : などはそのまま出力

戻り値は「文字列」

ここが最大の落とし穴です。

• 数値比較・JOIN・GROUP BYで暗黙変換が起きる
• ソート順が見た目依存になる

⇒ 集計・検索は日付型、表示はDATE_FORMAT()
この原則を守るだけで、トラブルの8割は防げます。

フォーマット指定子の体系理解

日付系（年・月・日）

• %Y：年（4桁）
• %y：年（2桁）
• %m：月（01–12）
• %c：月（1–12）
• %d：日（01–31）
• %e：日（1–31）
• %j：年内通算日（001–366）

⇒ 機械処理・ソート前提ならゼロ埋め（%Y %m %d）

時刻系（時・分・秒）

• %H：24時間（00–23）
• %k：24時間（0–23）
• %h %I：12時間（01–12）
• %i：分
• %s：秒
• %f：マイクロ秒

ログや高頻度イベントでは
%H:%i:%s.%f が非常に有効です。

曜日・月名・週番号

• %W %a：曜日名
• %M %b：月名
• %v %V %x %X：週番号・週年

⚠ lc_time_names に依存
表示専用に留め、キーには使わないのが鉄則です。

よく使うフォーマット例（即コピペ）

日付（YYYY-MM-DD）

DATE_FORMAT(date_col, '%Y-%m-%d')

日時（YYYY-MM-DD HH:MM:SS）

DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s')

月次集計キー

DATE_FORMAT(created_at, '%Y-%m')

日次集計キー

DATE_FORMAT(created_at, '%Y%m%d')

⇒ ORDER BYは元の日時列で

実務での代表的な使用シーン

レポート・CSV出力

• 列ごとにフォーマットを固定
• ASで列名を安定させる
• ソート用カラムと表示用カラムを分離

ローカライズ表示

• DB側でやるか、アプリ側でやるかを先に決める
• 曜日・月名は表示専用
• 環境差分を前提に設計

GROUP BYでの利用

GROUP BY DATE_FORMAT(created_at, '%Y-%m')

は便利ですが、

• 文字列キー
• 並び順依存
• ONLY_FULL_GROUP_BY注意

⇒ 集計は YEAR() MONTH() や範囲条件も検討。

注意点とベストプラクティス

WHERE句で使わない

×

WHERE DATE_FORMAT(created_at, '%Y-%m-%d') = '2026-01-28'

○

WHERE created_at >= '2026-01-28 00:00:00'
AND created_at < '2026-01-29 00:00:00'

インデックスが効くかどうかが決定的に違います。

タイムゾーン問題

• TIMESTAMPは time_zone の影響を受ける
• UTC保存＋表示時変換が王道

DATE_FORMAT(CONVERT_TZ(ts, '+00:00', '+09:00'), ...)

NULL・不正日付

• NULL → NULL
• ゼロ日付 → 00 が見える

⇒ 整形の問題ではなくデータ品質の問題

併用すると強い関連関数

STR_TO_DATE()

文字列 → 日時

STR_TO_DATE('2026-01-28', '%Y-%m-%d')

TIME_FORMAT()

時刻専用の整形

TIME_FORMAT(TIME(created_at), '%H:%i')

技術仕様として知っておくべき点

• 文字セット・照合順序の影響
• lc_time_namesによる言語差
• 週番号は定義が複数ある（ISO週か要確認）

公式ドキュメント：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

まとめ｜DATE_FORMAT()を「使える武器」にする

• DATE_FORMAT()は表示専用の整形関数
• 戻り値は文字列。検索・結合の軸にしない
• WHERE句では使わず、範囲条件に置き換える
• タイムゾーン・ロケール差分を前提に設計
• 集計・表示・検索の責務を分離する

ここを押さえれば、DATE_FORMAT()は
「事故を生む関数」から「信頼できる道具」に変わります。

次に進むあなたへ（求人のご案内）

この記事をここまで読み切ったあなたは、
「SQLをただ書く」のではなく、「長期運用できる設計」を考えるエンジニアだと思います。

私たちは、

• データ設計やパフォーマンスまで考え抜く
• 技術の背景や思想を大切にする
• 知識を共有し、チームで強くなる

そんなエンジニアが集まる環境を作っています。

もし、
「こういう技術の話が普通に通じるチームで働きたい」
と感じたなら、ぜひ私たちのエンジニア採用ページも覗いてみてください。

あなたと一緒に、“ちゃんとした技術”を積み上げていける仲間を探しています。