【保存版】MySQLでDATETIME型を自在に操る！文字列変換のテクニック集

MySQL で DATETIME 型の値を文字列に変換する方法

MySQL では、主に以下の2つの方法で DATETIME 型の値を文字列に変換できます。

DATE_FORMAT 関数は、DATETIME 型の値を指定されたフォーマットに従って文字列に変換します。基本的な構文は以下の通りです。

SELECT DATE_FORMAT(datetime_column, format_string) AS formatted_datetime;

• datetime_column: 変換したい DATETIME 型の列名
• format_string: 変換後の文字列形式を指定するフォーマット文字列

フォーマット文字列は、以下の指定子を使って自由に組み合わせて形式を定義できます。

例:

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

このクエリは、orders テーブルの created_at 列にある DATETIME 型の値を、YYYY-MM-DD HH:MM:SS 形式の文字列に変換して formatted_datetime 列に出力します。

STRFTIME 関数は、より詳細な書式設定と、様々な時間情報へのアクセスを提供します。構文は以下の通りです。

SELECT STRFTIME(format_string, datetime_value) AS formatted_datetime;

フォーマット文字列

STRFTIME 関数は、DATE_FORMAT 関数よりも多くのフォーマット指定子を提供しており、様々な時間情報 (例：曜日、タイムゾーン) にアクセスできます。詳細は、MySQL 8.0 リファレンスマニュアル を参照してください。

SELECT STRFTIME('%Y年 %m月 %d日 (%a) %H時%i分%s', created_at) AS formatted_datetime
FROM appointments;

補足

• 上記以外にも、CONCAT 関数や SUBSTRING 関数などを組み合わせて、より複雑なフォーマットの文字列を作成することもできます。
• 変換後の文字列は、別の列に格納したり、クエリ結果としてそのまま出力したりできます。

データベース操作を行う際は、十分な注意とテストを行い、誤ったデータ操作による問題が発生しないようにしてください。

DATE_FORMAT 関数を使用したサンプル

-- 現在時刻を YYYY-MM-DD HH:MM:SS 形式の文字列に変換
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted_datetime;

-- 特定の日付を指定形式の文字列に変換
SELECT DATE_FORMAT('2023-12-25 10:30:00', '%Y/%m/%d %H:%p') AS formatted_datetime;

-- orders テーブルの created_at 列の値をすべて YYYY-MM-DD 形式の文字列に変換
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS formatted_created_at
FROM orders;

-- 現在時刻を 曜日を含む形式の文字列に変換
SELECT STRFTIME('%Y年 %m月 %d日 (%a) %H時%i分%s', NOW()) AS formatted_datetime;

-- 特定の日付をタイムゾーン情報を含む形式の文字列に変換
SELECT STRFTIME('%Y-%m-%d %H:%M:%S %z', '2024-06-09 11:01:00+07:00') AS formatted_datetime;

-- employees テーブルの入社日を経過年数を含む形式の文字列に変換
SELECT
  CONCAT(employee_name, ' (',
    STRFTIME('%Y年 %mヶ月勤務', DATEDIFF(CURDATE(), hire_date)), ')'
  ) AS formatted_employee_info
FROM employees;

これらのサンプルコードはあくまでも一例であり、状況に合わせて様々な形式に変換できます。フォーマット文字列の組み合わせや、関数の使い方については、MySQL の公式ドキュメントを参照してください。

MySQL で DATETIME 型の値を文字列に変換するその他の方法

CONVERT 関数は、値を別のデータ型に変換する汎用関数です。DATETIME 型の値を文字列に変換する場合、以下のように使用できます。

SELECT CONVERT(datetime_value, CHAR(255)) AS formatted_datetime;

このクエリは、datetime_value を最大 255 文字の CHAR 型の文字列に変換します。変換後の文字列形式は、CONVERT 関数の引数として指定するフォーマット文字列によって決まります。

CAST 関数を使用する

SELECT CAST(datetime_value AS CHAR(255)) AS formatted_datetime;

文字列連結演算子 (+) を使用して、DATETIME 型の値を文字列に変換することもできます。ただし、この方法はフォーマット制御が限られるため、他の方法と比べて柔軟性に欠けます。

SELECT CONCAT(YEAR(datetime_value), '-', MONTH(datetime_value), '-',
         DAY(datetime_value), ' ', HOUR(datetime_value), ':',
         MINUTE(datetime_value), ':', SECOND(datetime_value)) AS formatted_datetime;

このクエリは、datetime_value を YYYY-MM-DD HH:MM:SS 形式の文字列に変換します。

サブクエリを使用して、DATETIME 型の値を文字列に変換することもできます。この方法は複雑になりますが、柔軟性と制御性に優れています。

SELECT (
  SELECT CONCAT(YEAR(d.datetime_value), '-', MONTH(d.datetime_value), '-',
                DAY(d.datetime_value), ' ', HOUR(d.datetime_value), ':',
                MINUTE(d.datetime_value), ':', SECOND(d.datetime_value))
  FROM dual AS d
  WHERE d.datetime_value = your_datetime_value
) AS formatted_datetime;

• シンプルで使いやすい場合は、DATE_FORMAT 関数を使用するのがおすすめです。
• より詳細なフォーマット設定が必要な場合は、STRFTIME 関数を使用します。
• 汎用性の高い方法が必要な場合は、CONVERT 関数または CAST 関数を使用します。
• 柔軟性と制御性に優れた方法が必要な場合は、サブクエリを使用します。