【MariaDB】DATE_FORMATの使い方：プログラミングでの日付フォーマット例

基本的な構文

DATE_FORMAT(date, format)

ここで、

• format: どのように日付や時刻をフォーマットするかを指定するフォーマット文字列です。
• date: フォーマットしたい日付または時刻の値を持つカラムや式を指定します。

主なフォーマット指定子

format文字列には、様々な指定子を含めることができます。以下に主なものをいくつか示します。

指定子	説明	例
%Y	西暦4桁 (例: 2023)	2023
%y	西暦下2桁 (例: 23)	23
%m	月を0埋めした数値 (01-12)	05
%c	月を数値 (1-12)	5
%M	月のフルネーム (例: January)	May
%b	月の省略名 (例: Jan)	May
%d	日を0埋めした数値 (01-31)	16
%e	日を数値 (1-31)	16
%H	時を24時間形式で0埋めした数値 (00-23)	13
%k	時を24時間形式で数値 (0-23)	13
%h	時を12時間形式で0埋めした数値 (01-12)	01
%l	時を12時間形式で数値 (1-12)	1
%i	分を0埋めした数値 (00-59)	12
%s	秒を0埋めした数値 (00-59)	34
%p	AM または PM	PM
%W	曜日のフルネーム (例: Monday)	Friday
%a	曜日の省略名 (例: Mon)	Fri
%w	曜日を数値 (0=日曜日, 6=土曜日)	5
%j	年の通算日 (001-366)	136
%%	リテラルの '%'	%

使用例

例えば、ordersテーブルにorder_dateという日付型のカラムがあるとします。この日付を「YYYY年MM月DD日」という形式で表示したい場合、次のようなSQLクエリを実行します。

SELECT DATE_FORMAT(order_date, '%Y年%m月%d日') AS formatted_date
FROM orders;

このクエリの結果として、formatted_dateというエイリアスで、指定した形式で日付が表示されます。例えば、order_dateが '2025-05-16' であれば、結果は '2025年05月16日' となります。

時刻のフォーマット

時刻に関する指定子も組み合わせて使用することで、時刻の表示形式も細かく制御できます。例えば、「HH時MM分SS秒」という形式で表示したい場合は以下のようになります。

SELECT DATE_FORMAT(order_time, '%H時%i分%s秒') AS formatted_time
FROM orders;

order_timeが '13:12:34' であれば、結果は '13時12分34秒' となります。

日付と時刻の組み合わせ

日付と時刻の両方を含むカラムに対して、両方のフォーマット指定子を組み合わせることも可能です。

SELECT DATE_FORMAT(order_datetime, '%Y-%m-%d %H:%i:%s') AS formatted_datetime
FROM orders;

order_datetimeが '2025-05-16 13:12:34' であれば、結果は '2025-05-16 13:12:34' となります。

• 存在しない日付や不正な形式のdateが渡された場合、結果はNULLになる可能性があります。
• DATE_FORMAT関数の第二引数であるフォーマット文字列は、大文字と小文字を区別します。例えば、%M は月のフルネームですが、%m は月を数値で表示します。

---

フォーマット指定子の誤り

最も一般的なエラーは、フォーマット文字列内の指定子を間違って記述することです。

• スペルミス
  フォーマット指定子のスペルを間違えることもあります。

  • 例
    %Yar のように、存在しない指定子を記述してしまう。
  • トラブルシューティング
    注意深くフォーマット文字列を見直し、スペルミスがないか確認してください。

• 大文字・小文字の区別
  フォーマット指定子は、大文字と小文字を区別します。例えば、%M（月のフルネーム）と %m（月の数値）は全く異なる結果をもたらします。

  • 例
    %m と書くべきところを %M と書いてしまう。
  • トラブルシューティング
    MariaDBのドキュメントやリファレンスを参照し、正しい指定子を使用しているか確認してください。目的の出力形式に対して、適切な大文字・小文字の指定子を選びましょう。

適用対象のデータ型が不正

DATE_FORMAT関数は、DATE、DATETIME、TIMESTAMP型の値を対象としています。これらの型ではないカラムや値を指定した場合、予期しない結果（NULLなど）が出力されることがあります。

• トラブルシューティング
  DESCRIBE テーブル名; などのコマンドで、対象カラムのデータ型を確認してください。必要であれば、CAST関数や CONVERT関数を使用して、適切なデータ型に変換してから DATE_FORMAT を適用することを検討してください。
• 例
  VARCHAR型のカラムに対して DATE_FORMAT を適用しようとする。

期待しない出力

エラーは発生しないものの、期待した形式で出力されない場合があります。

• 時間のずれ
  タイムゾーンの設定によっては、意図しない時間で表示されることがあります。これは DATE_FORMAT 自体の問題ではありませんが、関連するトラブルとして考慮する必要があります。

  • トラブルシューティング
    MariaDBのタイムゾーン設定 (time_zone システム変数) や、アプリケーション側のタイムゾーン設定を確認してください。必要に応じて CONVERT_TZ 関数を使用してタイムゾーンを変換することもできます。

• ゼロ埋めの有無
  指定子によっては、数値がゼロ埋めされるかどうかが異なります（例: %m はゼロ埋めあり、%c はゼロ埋めなし）。

  • トラブルシューティング
    出力形式の要件に合わせて、適切な指定子（%m vs %c、%d vs %e、%H vs %k、%h vs %l など）を選択してください。

NULL値の扱い

DATE_FORMAT に NULL の日付または時刻の値が渡された場合、通常は結果として NULL が返されます。

• トラブルシューティング
  COALESCE 関数などを使用して、NULL値を別のデフォルト値に置き換えることを検討してください（例: DATE_FORMAT(COALESCE(order_date, CURDATE()), '%Y-%m-%d')）。
• 例
  order_date カラムに NULL 値が含まれている場合に DATE_FORMAT(order_date, '%Y-%m-%d') を実行すると、その行の結果は NULL になります。

パフォーマンスの問題

大量のデータに対して DATE_FORMAT を頻繁に適用すると、クエリのパフォーマンスに影響を与える可能性があります。

• トラブルシューティング
  可能であれば、アプリケーション側で日付フォーマットを行う、または表示専用の計算済みカラムを作成することを検討してください。インデックスが適切に利用されているかどうかも確認してください。

トラブルシューティングの一般的な手順

1. SQLクエリの確認
   DATE_FORMAT 関数の構文、特にフォーマット文字列が正しいか、意図した指定子を使用しているかを確認します。
2. 対象データの確認
   SELECT カラム名 FROM テーブル名 LIMIT 10; などで、実際に処理しているデータの内容とデータ型を確認します。
3. MariaDBのエラーログの確認
   エラーが発生している場合は、MariaDBのエラーログに詳細な情報が記録されている可能性があります。
4. ドキュメントの参照
   MariaDBの公式ドキュメントで DATE_FORMAT 関数の詳細な仕様と使用例を確認します。
5. 簡単なテスト
   複雑なクエリの一部として DATE_FORMAT を使用している場合は、簡単なSELECT文で DATE_FORMAT 関数の動作を個別にテストしてみることをお勧めします。

---

PHPでの例

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password);
    // PDOのエラーモードを例外に設定
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 特定の日付をフォーマットして取得する例
    $stmt = $conn->prepare("SELECT DATE_FORMAT('2025-08-20', '%Y年%m月%d日') AS formatted_date");
    $stmt->execute();
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "フォーマットされた日付: " . $result['formatted_date'] . "\n"; // 出力: フォーマットされた日付: 2025年08月20日

    // テーブルから日付データをフォーマットして取得する例
    $stmt = $conn->prepare("SELECT order_id, DATE_FORMAT(order_date, '%Y/%m/%d') AS formatted_order_date FROM orders");
    $stmt->execute();
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo "\n注文IDとフォーマットされた注文日:\n";
    foreach ($results as $row) {
        echo "注文ID: " . $row['order_id'] . ", 注文日: " . $row['formatted_order_date'] . "\n";
    }

    // WHERE句で日付の範囲を指定し、フォーマットして表示する例
    $stmt = $conn->prepare("SELECT product_name, DATE_FORMAT(created_at, '%Y年%b月%d日 %H:%i') AS formatted_created_at FROM products WHERE created_at >= '2025-05-01'");
    $stmt->execute();
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo "\n2025年5月1日以降に作成された商品と作成日時:\n";
    foreach ($results as $row) {
        echo "商品名: " . $row['product_name'] . ", 作成日時: " . $row['formatted_created_at'] . "\n";
    }

} catch(PDOException $e) {
    echo "接続エラー: " . $e->getMessage();
} finally {
    $conn = null; // 接続を閉じる
}
?>

このPHPの例では、PDO (PHP Data Objects) を使用してMariaDBに接続し、以下の処理を行っています。

1. 特定の日付のフォーマット
   直接日付文字列 '2025-08-20' を DATE_FORMAT 関数でフォーマットしています。
2. テーブルデータのフォーマット
   orders テーブルの order_date カラムのデータを指定した形式で取得しています。
3. WHERE句と組み合わせたフォーマット
   products テーブルの created_at カラムで条件を指定し、その結果をフォーマットしています。

Pythonでの例

import mysql.connector

config = {
    'user': 'your_username',
    'password': 'your_password',
    'host': 'localhost',
    'database': 'your_database',
    'charset': 'utf8mb4'
}

try:
    cnx = mysql.connector.connect(**config)
    cursor = cnx.cursor()

    # 特定の日付をフォーマットして取得する例
    query = "SELECT DATE_FORMAT('2025-11-10', '%W, %M %d, %Y') AS formatted_date"
    cursor.execute(query)
    result = cursor.fetchone()
    print(f"フォーマットされた日付: {result[0]}") # 出力: フォーマットされた日付: Monday, November 10, 2025

    # テーブルから日付データをフォーマットして取得する例
    query = "SELECT customer_id, DATE_FORMAT(registration_date, '%y-%m-%d') AS formatted_registration_date FROM customers"
    cursor.execute(query)
    results = cursor.fetchall()

    print("\n顧客IDとフォーマットされた登録日:")
    for row in results:
        print(f"顧客ID: {row[0]}, 登録日: {row[1]}")

    # ORDER BY句と組み合わせたフォーマット
    query = "SELECT event_name, DATE_FORMAT(start_time, '%Y/%m/%d %H:%i:%s') AS formatted_start_time FROM events ORDER BY start_time DESC"
    cursor.execute(query)
    results = cursor.fetchall()

    print("\nイベント名とフォーマットされた開始時間 (降順):")
    for row in results:
        print(f"イベント名: {row[0]}, 開始時間: {row[1]}")

except mysql.connector.Error as err:
    print(f"接続エラー: {err}")

finally:
    if 'cnx' in locals() and cnx.is_connected():
        cursor.close()
        cnx.close()

このPythonの例では、mysql-connector-python ライブラリを使用してMariaDBに接続し、同様の処理を行っています。

1. 特定の日付のフォーマット
   直接日付文字列を DATE_FORMAT 関数でフォーマットしています。
2. テーブルデータのフォーマット
   customers テーブルの registration_date カラムのデータを指定した形式で取得しています。
3. ORDER BY句と組み合わせたフォーマット
   events テーブルの start_time カラムをフォーマットし、結果を降順にソートしています。

• テーブル名やカラム名は、実際に使用しているデータベースのスキーマに合わせて適宜変更してください。
• データベースへの接続情報（ホスト名、ユーザー名、パスワード、データベース名）は、ご自身の環境に合わせて変更してください。
• エラーハンドリングも基本的なもののみを示しています。より堅牢なアプリケーションでは、より詳細なエラー処理を行う必要があります。
• 上記のコード例では、プレースホルダ（? や :name`）を使ったSQLインジェクション対策は省略しています。実際のアプリケーションでは、セキュリティのために必ずプレースホルダを使用してください。

---

PHPでの代替方法

PHPには、日付と時刻を扱うための強力な機能が組み込まれています。MariaDBから日付や時刻のデータ（通常はUNIXタイムスタンプまたはMySQLのDATE/DATETIME/TIMESTAMP型）を取得した後、PHPの関数を使ってフォーマットすることができます。

• DateTime クラス
  よりオブジェクト指向な方法で日付と時刻を扱えます。タイムゾーンの操作なども容易に行えます。

  <?php
  $datetime_str = '2025-08-15 12:00:00';
  $datetime = new DateTime($datetime_str);
  
  $formatted_date = $datetime->format('Y年n月j日');
  echo "フォーマットされた日付 (DateTimeクラス): " . $formatted_date . "\n"; // 出力: フォーマットされた日付 (DateTimeクラス): 2025年8月15日
  
  $formatted_time = $datetime->format('H:i:s');
  echo "フォーマットされた時刻 (DateTimeクラス): " . $formatted_time . "\n"; // 出力: フォーマットされた時刻 (DateTimeクラス): 12:00:00
  
  // タイムゾーンを設定してフォーマット
  $datetime_utc = new DateTime($datetime_str, new DateTimeZone('UTC'));
  $datetime_jst = $datetime_utc->setTimezone(new DateTimeZone('Asia/Tokyo'));
  echo "日本時間でフォーマット (DateTimeクラス): " . $datetime_jst->format('Y-m-d H:i:s') . "\n"; // 出力例: 日本時間でフォーマット (DateTimeクラス): 2025-08-15 21:00:00
  ?>
  

• date() 関数
  UNIXタイムスタンプを様々な形式の文字列に変換します。MySQLからUNIXタイムスタンプとして日付や時刻を取得した場合や、PHPで生成したタイムスタンプをフォーマットする際に便利です。

  <?php
  // MySQLからUNIXタイムスタンプを取得したと仮定
  $timestamp = 1755327600; // 2025-08-15 12:00:00 UTC のタイムスタンプ
  
  $formatted_date = date('Y年m月d日 H時i分s秒', $timestamp);
  echo "フォーマットされた日付 (date関数): " . $formatted_date . "\n"; // 出力: フォーマットされた日付 (date関数): 2025年08月15日 21時00分00秒 (※タイムゾーン設定による)
  
  // MySQLのDATE/DATETIME/TIMESTAMP型を文字列として取得した場合
  $datetime_str = '2025-08-15 12:00:00';
  $timestamp_from_mysql = strtotime($datetime_str);
  $formatted_date_from_mysql = date('Y-m-d', $timestamp_from_mysql);
  echo "フォーマットされた日付 (strtotime + date): " . $formatted_date_from_mysql . "\n"; // 出力: フォーマットされた日付 (strtotime + date): 2025-08-15
  ?>
  

Pythonでの代替方法

Pythonにも、日付と時刻を扱うための標準ライブラリ datetime があります。

• pandas ライブラリ (データ分析の場合)
  データ分析でよく使われる pandas ライブラリの to_datetime() 関数を使うと、日付や時刻の文字列を datetime64 型のオブジェクトに変換し、その後 dt.strftime() メソッドでフォーマットできます。

  import pandas as pd
  
  date_series = pd.Series(['2025-08-15', '2025-08-16'])
  datetime_series = pd.to_datetime(date_series)
  
  formatted_series = datetime_series.dt.strftime('%Y年%m月')
  print("フォーマットされた日付 (pandas):")
  print(formatted_series)
  # 出力:
  # フォーマットされた日付 (pandas):
  # 0    2025年08月
  # 1    2025年08月
  # dtype: object
  

• datetime オブジェクトの strftime() メソッド
  datetime オブジェクトを、指定したフォーマットの文字列に変換します。

  from datetime import datetime
  
  # MySQLからDATE/DATETIME/TIMESTAMP型を文字列として取得したと仮定
  datetime_str = '2025-08-15 12:00:00'
  datetime_obj = datetime.strptime(datetime_str, '%Y-%m-%d %H:%M:%S')
  
  formatted_date = datetime_obj.strftime('%Y/%m/%d')
  print(f"フォーマットされた日付 (strftime): {formatted_date}") # 出力: フォーマットされた日付 (strftime): 2025/08/15
  
  formatted_datetime = datetime_obj.strftime('%a, %d %b %Y %H:%M:%S GMT')
  print(f"フォーマットされた日時 (strftime): {formatted_datetime}") # 出力例: フォーマットされた日時 (strftime): Fri, 15 Aug 2025 12:00:00 GMT
  

プログラミング側でフォーマットするメリットとデメリット

メリット

• テストの容易性
  フォーマット処理がアプリケーションのコード内で行われるため、単体テストなどが比較的容易になります。
• 柔軟なフォーマット
  プログラミング言語の機能を利用することで、より複雑な条件に基づいたフォーマットや、ロケールに合わせたフォーマットなどが容易に行えます。
• データベースへの負荷軽減
  フォーマット処理をデータベース側で行わないため、特に大量のデータを扱う場合にデータベースの負荷を軽減できます。

デメリット

• 一貫性の維持
  複数の場所で同様のフォーマットを行う場合、一貫性を保つための管理が必要になります。
• コードの複雑化
  SQLクエリがシンプルになる一方で、アプリケーション側のコードがやや複雑になる場合があります。
• アプリケーション側の処理負荷増加
  フォーマット処理をアプリケーション側で行うため、アプリケーションサーバーの負荷が増加する可能性があります。

どちらを選ぶべきか

どちらの方法を選ぶかは、アプリケーションの要件、データの量、パフォーマンスへの影響、開発チームのスキルセットなどによって異なります。

• データ分析など、プログラミング言語側で日付オブジェクトを扱う必要がある場合
  プログラミング側のライブラリを使用するのが自然です。
• 複雑なフォーマットや、SQLクエリ内で直接結果を整形したい場合
  DATE_FORMAT 関数が便利です。
• 単純なフォーマットで、データベースの負荷を減らしたい場合
  プログラミング側でのフォーマットが有効な場合があります。