今回は、MySQLの日付型や時刻型の関数で使うフォーマット指定子について説明します。
文字列から日付型への変換や、既存の日付データを別の形式（例：yyyymmdd→yyyy/mm/dd）に変換する場合、MySQLではフォーマット指定子で形式を指定する必要があります。
また、たとえば月の表記をゼロ埋めして2桁表示するのか、ゼロ埋めはしないのか、といった指定もフォーマット指定子の使い分けで実現することができます。
この記事では、MySQLの日付や時刻、また曜日や週番号などの用途で使用するフォーマット指定子を紹介します。
あわせて、MySQLの日付型・時刻型に関連する関数についても紹介します。
この章では、MySQLで文字列を日付型や時刻型データに変換するときに使用するフォーマット指定子について説明します。フォーマット指定子とはMySQLの一部の関数において、出力（変換）する日付・時刻データをどのような形式にするかを指定するために使います。
MySQLで使用する主なフォーマット指定子は下記のとおりです
| 日付型の指定子 | 説明 | 例 |
|---|---|---|
| %Y | 4桁の年 | 2024 |
| %m | 0埋めされた2桁の月 | 03 |
| %c | 0埋めなしの月 | 3 |
| %d | 0埋めされた2桁の日 | 09 |
| %e | 0埋めなしの日 | 9 |
| 時刻型の指定子 | 説明 | 例 |
|---|---|---|
| %H | 0埋めされた2桁の時間（24時間形式） | 09や13 |
| %k | 0埋めなしの時間（24時間形式） | 9や13 |
| %h、%I | 0埋めされた2桁の時間（12時間形式） | 09や01 |
| %l | 0埋めなしの時間（12時間形式） | 9や1 |
| %i | 0埋めされた2桁の分 | 05 |
| %s、%S | 0埋めされた2桁の秒 | 03 |
| %f | マイクロ秒 | 000000から999999 |
| %p | AMまたはPM | AM |
| その他の指定子 | 説明 | 例 |
|---|---|---|
| %W | 曜日名 | Monday |
| %a | 曜日の省略形 | Mon |
| %D | 日に接尾辞を付加 | 1st, 2nd, 3rd, 4th… |
| %M | 月名 | January |
| %b | 月の省略形 | Jan |
| %j | 年の通算日 | 001から366 |
| %u | 週番号（月曜日開始） | – |
| %U | 週番号（日曜日開始） | – |
| %v | 年の週番号（月曜日開始） | – |
| %V | 年の週番号（日曜日開始） | – |
※多くのフォーマット指定子では出力が英語表記になります。
例：x年1月1日（金曜日）
%uを使う場合
x年の最初の木曜日は1月7日になりますので、1月1日は前年の最後の週に属します。そのため、%uでの週番号は01ではなく、前年の最後の週番号となります。
%Uを使う場合
1月1日を含む週をその年の第1週とします。1月1日が週のどの日にあっても、その週を1週目として数えます。
この章では、MySQL独自の日付型・時刻型のフォーマット指定子を使う関数を説明します。
一般に、日付や時刻でフォーマット指定子を使う主要な関数は、STR_TO_DATE、DATE_FORMAT、TIME_FORMATの3つですが、参考情報としてその他の関数も含めて紹介します。
STR_TO_DATEは、文字列の情報を日付型に変換するときに使用する関数です。変換後は、MySQLの標準日付形式であるYYYY-MM-DDになります。
文字列を日付型に変換する
STR_TO_DATE(‘文字列’, ‘フォーマット’)
第1引数・・日付データを表す文字列
第2引数・・その文字列のフォーマットを定義する形式
yyyymmdd形式からの変換
SELECT STR_TO_DATE('20240301', '%Y%m%d');yyyy/mm/dd形式からの変換
SELECT STR_TO_DATE('2024/03/01', '%Y/%m/%d');yyyy年mm月dd日からの変換
SELECT STR_TO_DATE('2024年03月01日', '%Y年%c月%e日');DATE_TO_FORMATは、元データを指定した日付形式に変換するときに使用する関数です。
DATE_TO_FORMATは2024/03/09、または2024/3/9のように、2桁の際に0を含めるか含めないかの指定のほか、03/09/24（月/日/年）やMar-9といった、さまざまな任意の日付形式に変換することができます。
文字列を日付型に変換する
DATE_FORMAT(‘日付’, ‘フォーマット’)
第1引数・・変換したい日付データ（日付型）
第2引数・・変換後の日付のフォーマットを定義する形式
yyyymmdd形式への変換
SELECT DATE_FORMAT('2024-03-01', '%Y%m%d');yyyy/mm/dd形式への変換
SELECT DATE_FORMAT('2024-03-01', '%Y/%m/%d');yyyy年mm月dd日への変換
SELECT DATE_FORMAT('2024-03-01', '%Y年%c月%e日');TIME_TO_FORMATは、元データを指定した時刻形式に変換するときに使用する関数です。
TIME_TO_FORMATは16:37:12、または16時37分12秒のように、さまざまな任意の時刻形式に変換することができます。また、12時間・24時間形式の指定や、時間が2桁の際に0を含めるか含めないかの指定も可能です。
文字列を日付型に変換する
TIME_FORMAT(‘時刻’, ‘フォーマット’)
第1引数・・変換したい時刻データ（時刻型）
第2引数・・変換後の時刻のフォーマットを定義する形式
HH:mm:ss形式への変換
SELECT TIME_FORMAT('15:30:45', '%H:%i:%s');h時m分s秒形式への変換
SELECT TIME_FORMAT('15:30:45', '%h時%i分%s秒');午前/午後 h時m分への変換
SELECT TIME_FORMAT('15:30:45', '%p %h時%i分');前節で紹介したSTR_TO_DATE、DATE_FORMAT、TIME_FORMAT以外でも、日付・時刻に関連する関数には下記のようなものがあります。
UNIX_TIMESTAMPとWEEKでは直接的にフォーマット指定子を使いませんが、日付・時刻型の関連関数として紹介しておきます。
UNIXタイムスタンプを日付や時刻の文字列に変換します。タイムスタンプとフォーマット指定子を引数に取り、フォーマットされた日付や時刻の文字列を返します。
SELECT FROM_UNIXTIME(1700000000, '%Y-%m-%d %H:%i:%s');日付や時刻をUNIXタイムスタンプ（1970年1月1日からの秒数）に変換します。日付や時刻の文字列を引数に取り、そのUNIXタイムスタンプを返します。
SELECT UNIX_TIMESTAMP('2024-03-01 12:00:00');日付から週番号を抽出します。年の始まりや週の始まり（日曜日や月曜日）を指定することで、週番号を取得できます。
SELECT WEEK('2024-03-01');今回は、MySQLの日付型・時刻型の関数で使用するフォーマット指定子について説明しました。
記事で取り上げたフォーマット指定子は、標準SQLではなくMySQL独自のものであるため、SQL全般の書籍などにも掲載されていない場合があります。
主要なフォーマット指定子は紹介したつもりですが、より専門性の高い用途で使用する場合には、MySQL公式のドキュメントを確認するとよいでしょう。