MySQL - 日付・時刻関数のソースを表示

== 概要 ==
MySQLは、日付と時刻を扱うための豊富な関数セットを提供している。<br>
日付・時刻データ型には、DATE、TIME、DATETIME、TIMESTAMP、YEARの5つが存在する。<br>
<br>
日付・時刻関数は、以下に示す用途で使用される。<br>
<br>
<center>
{| class="wikitable"
|+ 日付・時刻関数の用途
|-
! 用途 !! 主な関数
|-
| 現在日時の取得 || NOW、CURDATE、CURTIME等
|-
| 日付のフォーマット変換 || DATE_FORMAT、STR_TO_DATE等
|-
| 日付の加算・減算 || DATE_ADD、DATE_SUB、INTERVAL演算子等
|-
| 日付の差分計算 || DATEDIFF、TIMESTAMPDIFF等
|-
| 日付の部分取得 || YEAR、MONTH、DAY、EXTRACT等
|-
| タイムゾーン変換 || CONVERT_TZ等
|-
| UNIXタイムスタンプ変換 || UNIX_TIMESTAMP、FROM_UNIXTIME等
|}
</center>
<br>
これらの関数を効果的に使用することで、複雑な日付計算、レポート生成、データ分析を実現できる。<br>
<br>
日付・時刻型は、フォーマット、範囲、サイズ、タイムゾーン対応の点で異なる特性を持つ。<br>
TIMESTAMP型は、タイムゾーン対応と自動初期化機能を持つ点で、DATETIME型と異なる。<br>
<br>
MySQL 5.6.4以降では、DATETIME、TIMESTAMP、TIME型において、小数秒精度 (fsp : fractional seconds precision) がサポートされている。<br>
小数秒精度は、0 (秒単位) から6 (マイクロ秒単位) まで指定できる。<br>
<br>
<u>日付・時刻関数を使用する時は、インデックスの有効性、NULL値の扱い、タイムゾーン設定に注意する必要がある。</u><br>
<u>特に、<code>WHERE</code> 句で日付カラムに関数を適用すると、インデックスが無効化されるため、範囲検索に変換することが推奨される。</u><br>
<br><br>

== データ型 ==
==== データ型の比較 ====
MySQLの日付・時刻データ型を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ MySQL 日付・時刻データ型の比較
! データ型 !! 範囲 !! フォーマット !! サイズ !! fsp対応 !! タイムゾーン対応
|-
| DATE || '1000-01-01' ～ '9999-12-31' || YYYY-MM-DD || 3バイト || 非対応 || 非対応
|-
| TIME || '-838:59:59' ～ '838:59:59' || HH:MM:SS || 3バイト || 対応 (0-6) || 非対応
|-
| DATETIME || '1000-01-01 00:00:00' ～ '9999-12-31 23:59:59' || YYYY-MM-DD HH:MM:SS || 8バイト || 対応 (0-6) || 非対応
|-
| TIMESTAMP || '1970-01-01 00:00:01' UTC ～ '2038-01-19 03:14:07' UTC || YYYY-MM-DD HH:MM:SS || 4バイト || 対応 (0-6) || 対応 (UTC変換)
|-
| YEAR || 1901 ～ 2155、0000 || YYYY || 1バイト || 非対応 || 非対応
|}
</center>
<br>
DATE型は、日付のみを格納し、時刻部分を持たない。<br>
TIME型は、時刻または経過時間を格納し、負の値をサポートしている。<br>
<br>
DATETIME型は、タイムゾーン非対応であり、格納時の値がそのまま保持される。<br>
TIMESTAMP型は、格納時にUTCに変換され、取得時にセッション時刻に変換される。<br>
<br>
TIMESTAMP型の範囲は、UNIXタイムスタンプの制約により、2038年問題の影響を受ける。<br>
2038年以降の日付を扱う場合は、DATETIME型の使用を推奨する。<br>
<br>
YEAR型は、年のみを格納する。<br>
<u>YEAR(2)型 (2桁年) は、MySQL 8.0で廃止された。</u><br>
<br>
==== 小数秒精度 (fsp) ====
MySQL 5.6.4以降では、DATETIME、TIMESTAMP、TIME型において、小数秒精度 (fsp) がサポートされている。<br>
<br>
小数秒精度の指定値と精度の対応を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ 小数秒精度 (fsp) の指定
! fsp値 !! 精度 !! 例
|-
| 0 || 秒単位 (デフォルト) || 2024-01-15 12:30:45
|-
| 1 || 0.1秒単位 || 2024-01-15 12:30:45.1
|-
| 2 || 0.01秒単位 (センチ秒) || 2024-01-15 12:30:45.12
|-
| 3 || 0.001秒単位 (ミリ秒) || 2024-01-15 12:30:45.123
|-
| 4 || 0.0001秒単位 || 2024-01-15 12:30:45.1234
|-
| 5 || 0.00001秒単位 || 2024-01-15 12:30:45.12345
|-
| 6 || 0.000001秒単位 (マイクロ秒) || 2024-01-15 12:30:45.123456
|}
</center>
<br>
カラム定義での小数秒精度の指定例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_datetime DATETIME(3),   -- ミリ秒精度
    event_timestamp TIMESTAMP(6), -- マイクロ秒精度
    event_time TIME(3)             -- ミリ秒精度
 );
 </syntaxhighlight>
<br>
小数秒精度を指定すると、ストレージサイズが増加する。<br>
fsp 1〜2は1バイト、fsp 3〜4は2バイト、fsp 5〜6は3バイトの追加ストレージが必要となる。<br>
<br><br>

== 現在日時の取得 ==
現在の日付・時刻を取得する関数を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ 現在日時取得関数
! 関数 !! 戻り値 !! 説明
|-
| NOW() / CURRENT_TIMESTAMP / CURRENT_TIMESTAMP() || DATETIME || クエリ実行開始時点の固定値を返す
|-
| SYSDATE() || DATETIME || 関数実行時点の現在日時を返す (NOW()と異なる)
|-
| CURDATE() / CURRENT_DATE / CURRENT_DATE() || DATE || 現在の日付を返す
|-
| CURTIME() / CURRENT_TIME / CURRENT_TIME() || TIME || 現在の時刻を返す
|-
| UTC_DATE() || DATE || 現在のUTC日付を返す
|-
| UTC_TIME() || TIME || 現在のUTC時刻を返す
|-
| UTC_TIMESTAMP() || DATETIME || 現在のUTCタイムスタンプを返す
|}
</center>
<br>
<code>NOW()</code> 関数は、クエリ実行開始時点の日時を返す。<br>
同一クエリ内で複数回呼び出しても、同じ値を返す。<br>
<br>
<code>SYSDATE()</code> 関数は、関数実行時点の日時を返す。<br>
同一クエリ内で複数回呼び出すと、異なる値を返す可能性がある。<br>
<br>
<code>NOW()</code> と <code>SYSDATE()</code> の違いを示す例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT NOW(), SLEEP(2), NOW();          -- 同じ値を返す
 SELECT SYSDATE(), SLEEP(2), SYSDATE();  -- 2秒の差がある
 </syntaxhighlight>
<br>
UTC系関数は、サーバーのタイムゾーン設定に依存せず、常にUTC時刻を返す。<br>
国際的なアプリケーションでは、UTC系関数の使用を推奨する。<br>
<br>
小数秒精度を指定する場合は、引数でfsp値を指定する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT NOW(3);      -- ミリ秒精度の現在日時
 SELECT CURTIME(6);  -- マイクロ秒精度の現在時刻
 </syntaxhighlight>
<br><br>

== 日付・時刻のフォーマット ==
==== DATE_FORMAT / TIME_FORMAT ====
日付・時刻を文字列に変換する関数を以下に示す。<br>
<br>
 DATE_FORMAT(date, format)
 TIME_FORMAT(time, format)
<br>
<code>DATE_FORMAT()</code> 関数は、日付または日付時刻値を指定フォーマットで文字列に変換する。<br>
<code>TIME_FORMAT()</code> 関数は、時刻値を指定フォーマットで文字列に変換する。(時刻関連指定子のみ使用可能)<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
 -- 出力例: 2026-02-15 14:30:45
 
 SELECT DATE_FORMAT('2024-03-15', '%Y年%m月%d日');
 -- 出力例: 2024年03月15日
 
 SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y');
 -- 出力例: Saturday, February 15, 2026
 
 SELECT TIME_FORMAT('14:30:45', '%H時%i分%s秒');
 -- 出力例: 14時30分45秒
 </syntaxhighlight>
<br>
==== フォーマット指定子一覧 ====
主要なフォーマット指定子を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ DATE_FORMAT フォーマット指定子
! 指定子 !! 説明 !! 例
|-
| %Y || 4桁の年 || 2026
|-
| %y || 2桁の年 || 26
|-
| %m || 2桁の月 (01-12) || 02
|-
| %c || 月 (1-12) || 2
|-
| %M || 月名 || February
|-
| %b || 月名の略 || Feb
|-
| %d || 2桁の日 (01-31) || 15
|-
| %e || 日 (1-31) || 15
|-
| %D || 英語序数付き日 || 15th
|-
| %H || 2桁の時 (00-23) || 14
|-
| %h / %I || 2桁の時 (01-12) || 02
|-
| %k || 時 (0-23) || 14
|-
| %l || 時 (1-12) || 2
|-
| %i || 2桁の分 (00-59) || 30
|-
| %s / %S || 2桁の秒 (00-59) || 45
|-
| %f || マイクロ秒 (6桁) || 123456
|-
| %p || AM / PM || PM
|-
| %W || 曜日名 || Saturday
|-
| %a || 曜日名の略 || Sat
|-
| %w || 曜日番号 (0=日曜 - 6=土曜) || 6
|-
| %j || 年内の日数 (001-366) || 046
|-
| %U || 週番号 (00-53、日曜始まり) || 06
|-
| %u || 週番号 (00-53、月曜始まり) || 06
|-
| %V || 週番号 (01-53、日曜始まり、%Xと併用) || 07
|-
| %v || 週番号 (01-53、月曜始まり、%xと併用) || 07
|-
| %X || 4桁の年 (%Vと併用) || 2026
|-
| %x || 4桁の年 (%vと併用) || 2026
|}
</center>
<br>
複数の指定子を組み合わせて、任意のフォーマットを作成できる。<br>
<br>
日本語形式の日付フォーマット例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 (%a) %H:%i:%s');
 -- 出力例: 2026年02月15日 (Sat) 14:30:45
 </syntaxhighlight>
<br><br>

== 日付の加算・減算 ==
==== DATE_ADD / DATE_SUB ====
日付に指定した間隔を加算・減算する関数を以下に示す。<br>
<br>
 DATE_ADD(date, INTERVAL expr unit)
 DATE_SUB(date, INTERVAL expr unit)
<br>
<code>ADDDATE()</code> 関数、<code>SUBDATE()</code> 関数は、DATE_ADD()、DATE_SUB()の別名である。<br>
<br>
演算子形式でも使用できる。<br>
 date + INTERVAL expr unit
 date - INTERVAL expr unit
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 現在日時に3日を加算
 SELECT DATE_ADD(NOW(), INTERVAL 3 DAY);
 
 -- 現在日時から2時間を減算
 SELECT DATE_SUB(NOW(), INTERVAL 2 HOUR);
 
 -- 演算子形式での加算
 SELECT NOW() + INTERVAL 1 MONTH;
 
 -- 演算子形式での減算
 SELECT NOW() - INTERVAL 7 DAY;
 
 -- 複数の間隔を組み合わせ
 SELECT NOW() + INTERVAL 1 YEAR + INTERVAL 6 MONTH;
 
 -- 複合単位の使用
 SELECT NOW() + INTERVAL '1 6' YEAR_MONTH;  -- 1年6ヶ月を加算
 </syntaxhighlight>
<br>
月末の自動調整が行われる。<br>
例として、1月31日に1ヶ月を加算すると、2月28日 (または2月29日) が返される。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH);
 -- 出力: 2024-02-29 (2024年は閏年)
 
 SELECT DATE_ADD('2025-01-31', INTERVAL 1 MONTH);
 -- 出力: 2025-02-28
 </syntaxhighlight>
<br>
==== INTERVAL単位一覧 ====
INTERVAL句で使用できる単位を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ INTERVAL単位
! 単位 !! 説明 !! expr形式 !! 例
|-
| MICROSECOND || マイクロ秒 || 整数 || INTERVAL 500000 MICROSECOND
|-
| SECOND || 秒 || 整数 || INTERVAL 30 SECOND
|-
| MINUTE || 分 || 整数 || INTERVAL 15 MINUTE
|-
| HOUR || 時 || 整数 || INTERVAL 3 HOUR
|-
| DAY || 日 || 整数 || INTERVAL 7 DAY
|-
| WEEK || 週 || 整数 || INTERVAL 2 WEEK
|-
| MONTH || 月 || 整数 || INTERVAL 3 MONTH
|-
| QUARTER || 四半期 || 整数 || INTERVAL 1 QUARTER
|-
| YEAR || 年 || 整数 || INTERVAL 5 YEAR
|-
| SECOND_MICROSECOND || 秒.マイクロ秒 || 'ss.mmmmmm' || INTERVAL '30.500000' SECOND_MICROSECOND
|-
| MINUTE_MICROSECOND || 分:秒.マイクロ秒 || 'mm:ss.mmmmmm' || INTERVAL '15:30.500000' MINUTE_MICROSECOND
|-
| MINUTE_SECOND || 分:秒 || 'mm:ss' || INTERVAL '15:30' MINUTE_SECOND
|-
| HOUR_MICROSECOND || 時:分:秒.マイクロ秒 || 'hh:mm:ss.mmmmmm' || INTERVAL '3:15:30.500000' HOUR_MICROSECOND
|-
| HOUR_SECOND || 時:分:秒 || 'hh:mm:ss' || INTERVAL '3:15:30' HOUR_SECOND
|-
| HOUR_MINUTE || 時:分 || 'hh:mm' || INTERVAL '3:15' HOUR_MINUTE
|-
| DAY_MICROSECOND || 日 時:分:秒.マイクロ秒 || 'dd hh:mm:ss.mmmmmm' || INTERVAL '7 3:15:30.500000' DAY_MICROSECOND
|-
| DAY_SECOND || 日 時:分:秒 || 'dd hh:mm:ss' || INTERVAL '7 3:15:30' DAY_SECOND
|-
| DAY_MINUTE || 日 時:分 || 'dd hh:mm' || INTERVAL '7 3:15' DAY_MINUTE
|-
| DAY_HOUR || 日 時 || 'dd hh' || INTERVAL '7 3' DAY_HOUR
|-
| YEAR_MONTH || 年-月 || 'yy-mm' || INTERVAL '1-6' YEAR_MONTH
|}
</center>
<br>
複合単位を使用することで、複数の時間単位を1度に加算できる。<br>
<br>
==== ADDTIME / SUBTIME ====
<code>TIME</code> 値 または <code>DATETIME</code> 値に時間を加算・減算する関数を以下に示す。<br>
<br>
 ADDTIME(expr1, expr2)
 SUBTIME(expr1, expr2)
<br>
第1引数は、<code>TIME</code> 型 または <code>DATETIME</code> 型の値である。<br>
第2引数は、加算・減算する時間 (TIME型) である。<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT ADDTIME('2024-01-15 12:00:00', '02:30:00');
 -- 出力: 2024-01-15 14:30:00
 
 SELECT SUBTIME('2024-01-15 12:00:00', '01:15:00');
 -- 出力: 2024-01-15 10:45:00
 
 SELECT ADDTIME('10:30:00', '00:45:00');
 -- 出力: 11:15:00
 </syntaxhighlight>
<br><br>

== 日付の差分計算 ==
==== DATEDIFF ====
2つの日付の日数差分を返す関数を以下に示す。<br>
<br>
 DATEDIFF(date1, date2)
<br>
<u>戻り値は、<math>\mbox{date1} - \mbox{date2}</math> の日数差分である。</u><br>
時刻部分は無視される。<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT DATEDIFF('2024-02-15', '2024-01-15');
 -- 出力: 31
 
 SELECT DATEDIFF('2024-01-15', '2024-02-15');
 -- 出力: -31
 
 SELECT DATEDIFF(NOW(), '2024-01-01');
 -- 出力: 現在日と2024-01-01の日数差分
 
 -- 時刻部分は無視される
 SELECT DATEDIFF('2024-02-15 23:59:59', '2024-02-15 00:00:00');
 -- 出力: 0
 </syntaxhighlight>
<br>
==== TIMESTAMPDIFF ====
2つの日付・時刻の差分を指定単位で返す関数を以下に示す。<br>
<br>
 TIMESTAMPDIFF(unit, datetime1, datetime2)
<br>
<u>戻り値は、<math>\mbox{datetime2} - \mbox{datetime1}</math> の差分である。</u><br>
<u>第1引数は、MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEARのいずれかである。</u><br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT TIMESTAMPDIFF(DAY, '2024-01-15', '2024-02-15');
 -- 出力: 31
 
 SELECT TIMESTAMPDIFF(HOUR, '2024-01-15 10:00:00', '2024-01-15 14:30:00');
 -- 出力: 4
 
 SELECT TIMESTAMPDIFF(MINUTE, '2024-01-15 10:00:00', '2024-01-15 14:30:00');
 -- 出力: 270
 
 SELECT TIMESTAMPDIFF(MONTH, '2024-01-15', '2024-06-15');
 -- 出力: 5
 
 SELECT TIMESTAMPDIFF(YEAR, '2020-01-01', '2024-01-01');
 -- 出力: 4
 </syntaxhighlight>
<br>
<code>TIMESTAMPDIFF()</code> 関数は、引数の順序が <code>DATEDIFF()</code> 関数と逆であることに注意する。<br>
<br>
==== TIMEDIFF ====
2つの時刻の差分を返す関数を以下に示す。<br>
<br>
 TIMEDIFF(expr1, expr2)
<br>
戻り値は、<math>\mbox{expr1} - \mbox{expr2}</math> の時刻差分 (TIME型) である。<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT TIMEDIFF('14:30:00', '10:00:00');
 -- 出力: 04:30:00
 
 SELECT TIMEDIFF('2024-01-15 14:30:00', '2024-01-15 10:00:00');
 -- 出力: 04:30:00
 
 SELECT TIMEDIFF('10:00:00', '14:30:00');
 -- 出力: -04:30:00
 </syntaxhighlight>
<br><br>

== 日付・時刻の部分取得 ==
==== EXTRACT関数 ====
日付・時刻値から特定の部分を抽出する関数を以下に示す。<br>
<br>
 EXTRACT(<抽出する日付・時刻の単位> FROM <対象の日付・時刻値>)
<br>
<u><抽出する日付・時刻の単位></u> は、YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、MICROSECOND等である。<br>
<u><対象の日付・時刻値></u> は、DATE型、DATETIME型、TIMESTAMP型である。<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT EXTRACT(YEAR FROM '2024-02-15 14:30:45');
 -- 出力: 2024
 
 SELECT EXTRACT(MONTH FROM '2024-02-15 14:30:45');
 -- 出力: 2
 
 SELECT EXTRACT(DAY FROM '2024-02-15 14:30:45');
 -- 出力: 15
 
 SELECT EXTRACT(HOUR FROM '2024-02-15 14:30:45');
 -- 出力: 14
 
 SELECT EXTRACT(YEAR_MONTH FROM '2024-02-15');
 -- 出力: 202402
 </syntaxhighlight>
<br>
==== 個別の抽出関数 ====
日付・時刻の各部分を抽出する専用関数を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ 日付・時刻部分抽出関数
! 関数 !! 戻り値 !! 説明
|-
| YEAR(date) || 整数 || 年 (1000-9999)
|-
| MONTH(date) || 整数 || 月 (1-12)
|-
| DAY(date) / DAYOFMONTH(date) || 整数 || 日 (1-31)
|-
| HOUR(time) || 整数 || 時 (0-23、TIME型は-838～838)
|-
| MINUTE(time) || 整数 || 分 (0-59)
|-
| SECOND(time) || 整数 || 秒 (0-59)
|-
| MICROSECOND(datetime) || 整数 || マイクロ秒 (0-999999)
|-
| QUARTER(date) || 整数 || 四半期 (1-4)
|-
| WEEK(date [, mode]) || 整数 || 週番号 (0-53)
|-
| DAYOFWEEK(date) || 整数 || 曜日番号 (1=日曜 - 7=土曜)
|-
| WEEKDAY(date) || 整数 || 曜日番号 (0=月曜 - 6=日曜)
|-
| DAYOFYEAR(date) || 整数 || 年内の日数 (1-366)
|-
| DAYNAME(date) || 文字列 || 曜日名 (Monday - Sunday)
|-
| MONTHNAME(date) || 文字列 || 月名 (January - December)
|-
| LAST_DAY(date) || DATE || 月の最終日
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT YEAR('2024-02-15'), MONTH('2024-02-15'), DAY('2024-02-15');
 -- 出力: 2024, 2, 15
 
 SELECT HOUR('14:30:45'), MINUTE('14:30:45'), SECOND('14:30:45');
 -- 出力: 14, 30, 45
 
 SELECT DAYNAME('2024-02-15');
 -- 出力: Thursday
 
 SELECT MONTHNAME('2024-02-15');
 -- 出力: February
 
 SELECT DAYOFWEEK('2024-02-15');
 -- 出力: 5 (木曜日)
 
 SELECT WEEKDAY('2024-02-15');
 -- 出力: 3 (木曜日)
 
 SELECT QUARTER('2024-02-15');
 -- 出力: 1
 
 SELECT LAST_DAY('2024-02-15');
 -- 出力: 2024-02-29 (閏年)
 </syntaxhighlight>
<br>
<code>LAST_DAY()</code> 関数は、指定月の最終日を返す。<br>
閏年の2月の場合は、2月29日を返す。<br>
<br><br>

== 文字列と日付の変換 ==
==== STR_TO_DATE ====
文字列を日付・時刻値に変換する関数を以下に示す。<br>
<br>
 STR_TO_DATE(str, format)
<br>
第1引数は、変換する文字列である。<br>
第2引数は、第1引数のフォーマットを指定する。(DATE_FORMAT()と同じ指定子)<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT STR_TO_DATE('2024-02-15', '%Y-%m-%d');
 -- 出力: 2024-02-15
 
 SELECT STR_TO_DATE('02/15/2024', '%m/%d/%Y');
 -- 出力: 2024-02-15
 
 SELECT STR_TO_DATE('2024年02月15日', '%Y年%m月%d日');
 -- 出力: 2024-02-15
 
 SELECT STR_TO_DATE('15.02.2024 14:30:45', '%d.%m.%Y %H:%i:%s');
 -- 出力: 2024-02-15 14:30:45
 
 SELECT STR_TO_DATE('February 15, 2024', '%M %d, %Y');
 -- 出力: 2024-02-15
 </syntaxhighlight>
<br>
<code>STR_TO_DATE()</code> 関数は、変換に失敗した場合はNULLを返す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT STR_TO_DATE('invalid date', '%Y-%m-%d');
 -- 出力: NULL
 </syntaxhighlight>
<br>
==== 暗黙の型変換 ====
MySQLは、文字列を自動的に日付値に変換する。<br>
<br>
暗黙の型変換の例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT '2024-02-15' + INTERVAL 1 DAY;
 -- 出力: 2024-02-16 (文字列が自動的に日付に変換される)
 
 SELECT YEAR('2024-02-15');
 -- 出力: 2024 (文字列が自動的に日付に変換される)
 
 SELECT * FROM events WHERE event_date = '2024-02-15';
 -- event_date列がDATE型の場合、文字列が自動的に日付に変換される
 </syntaxhighlight>
<br>
ただし、明示的な変換を推奨する。<br>
暗黙の型変換は、予期しない結果を招く可能性がある。<br>
<br><br>

== UNIXタイムスタンプ ==
UNIXタイムスタンプ (1970-01-01 00:00:00 UTCからの秒数) と日付の変換関数を以下に示す。<br>
<br>
 UNIX_TIMESTAMP([date])
 FROM_UNIXTIME(unix_timestamp [, format])
<br>
<code>UNIX_TIMESTAMP()</code> 関数は、引数なしの場合は現在のUNIXタイムスタンプ、引数ありの場合は指定日時のUNIXタイムスタンプを返す。<br>
<code>FROM_UNIXTIME()</code> 関数は、UNIXタイムスタンプを日付・時刻値に変換する。<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT UNIX_TIMESTAMP();
 -- 出力例: 1739628000 (現在のUNIXタイムスタンプ)
 
 SELECT UNIX_TIMESTAMP('2024-01-01 00:00:00');
 -- 出力: 1704067200
 
 SELECT FROM_UNIXTIME(1704067200);
 -- 出力: 2024-01-01 00:00:00
 
 SELECT FROM_UNIXTIME(1704067200, '%Y年%m月%d日');
 -- 出力: 2024年01月01日
 
 -- 現在時刻との往復変換
 SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
 -- 出力: 現在の日時
 </syntaxhighlight>
<br>
<code>UNIX_TIMESTAMP()</code> 関数は、BIGINT型 (符号あり64ビット整数) を返す。<br>
<br>
タイムゾーンの影響を受けるため、注意が必要である。<br>
<code>UNIX_TIMESTAMP()</code> 関数は、セッション時刻をUTCに変換してからタイムスタンプを計算する。<br>
<code>FROM_UNIXTIME()</code> 関数は、UTCタイムスタンプをセッション時刻に変換する。<br>
<br><br>

== その他の日付関数 ==
その他の日付・時刻関数を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ その他の日付・時刻関数
! 関数 !! 説明 !! 例
|-
| MAKEDATE(year, dayofyear) || 年と年内日数から日付を生成 || MAKEDATE(2024, 46) → 2024-02-15
|-
| MAKETIME(hour, minute, second) || 時、分、秒から時刻を生成 || MAKETIME(14, 30, 45) → 14:30:45
|-
| SEC_TO_TIME(seconds) || 秒数をTIME値に変換 || SEC_TO_TIME(3665) → 01:01:05
|-
| TIME_TO_SEC(time) || TIME値を秒数に変換 || TIME_TO_SEC('01:01:05') → 3665
|-
| TO_DAYS(date) || 日付を通算日数に変換 (紀元0年からの日数) || TO_DAYS('2024-02-15') → 739299
|-
| FROM_DAYS(n) || 通算日数を日付に変換 || FROM_DAYS(739299) → 2024-02-15
|-
| GET_FORMAT(type, format_name) || ロケール別フォーマット文字列を取得 || GET_FORMAT(DATE, 'EUR') → '%d.%m.%Y'
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT MAKEDATE(2024, 100);
 -- 出力: 2024-04-09 (2024年の100日目)
 
 SELECT MAKETIME(14, 30, 45);
 -- 出力: 14:30:45
 
 SELECT SEC_TO_TIME(86400);
 -- 出力: 24:00:00
 
 SELECT TIME_TO_SEC('24:00:00');
 -- 出力: 86400
 
 SELECT TO_DAYS('2024-02-15');
 -- 出力: 739299
 
 SELECT FROM_DAYS(739299);
 -- 出力: 2024-02-15
 
 SELECT DATE_FORMAT('2024-02-15', GET_FORMAT(DATE, 'EUR'));
 -- 出力: 15.02.2024
 
 SELECT DATE_FORMAT('2024-02-15', GET_FORMAT(DATE, 'USA'));
 -- 出力: 02.15.2024
 </syntaxhighlight>
<br>
<code>GET_FORMAT()</code> 関数の形式名には、EUR (ヨーロッパ)、USA (アメリカ)、JIS (日本)、ISO (国際標準)、INTERNAL (MySQL内部形式) が使用できる。<br>
<br><br>

== タイムゾーン ==
MySQLは、タイムゾーン変換機能を提供している。<br>
<br>
タイムゾーン変換関数を以下に示す。<br>
 CONVERT_TZ(<変換対象の日付・時刻値>, <変換元のタイムゾーン  例: '+00:00'、'UTC'>,  <変換先のタイムゾーン  例: '+09:00'、'Asia/Tokyo'>)
<br>
第1引数を、第2引数タイムゾーンから第3引数タイムゾーンに変換する。<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT CONVERT_TZ('2024-02-15 12:00:00', 'UTC', 'Asia/Tokyo');
 -- 出力: 2024-02-15 21:00:00 (UTC+9時間)
 
 SELECT CONVERT_TZ('2024-02-15 12:00:00', 'Asia/Tokyo', 'America/New_York');
 -- 出力: 2024-02-14 22:00:00 (東京からニューヨークへの変換)
 
 SELECT CONVERT_TZ(NOW(), '+00:00', '+09:00');
 -- 出力: UTCから日本時間への変換
 </syntaxhighlight>
<br>
タイムゾーン設定は、セッションレベルまたはグローバルレベルで設定できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- セッションタイムゾーンの確認
 SELECT @@session.time_zone;
 
 -- グローバルタイムゾーンの確認
 SELECT @@global.time_zone;
 
 -- セッションタイムゾーンの設定
 SET time_zone = 'Asia/Tokyo';
 SET time_zone = '+09:00';
 
 -- グローバルタイムゾーンの設定 (要SUPER権限)
 SET GLOBAL time_zone = 'Asia/Tokyo';
 </syntaxhighlight>
<br>
TIMESTAMP型は、格納時にUTCに変換され、取得時にセッション時刻に自動変換される。<br>
<br>
TIMESTAMP型の自動変換例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- セッション時刻を日本時間に設定
 SET time_zone = 'Asia/Tokyo';
 
 CREATE TABLE logs (
    log_id INT PRIMARY KEY,
    log_timestamp TIMESTAMP
 );
 
 -- 日本時間で挿入
 INSERT INTO logs VALUES (1, '2024-02-15 12:00:00');
 
 -- セッション時刻をUTCに変更
 SET time_zone = 'UTC';
 
 -- UTCで取得される (12:00:00 - 9時間 = 03:00:00)
 SELECT log_timestamp FROM logs WHERE log_id = 1;
 -- 出力: 2024-02-15 03:00:00
 </syntaxhighlight>
<br>
<u>DATETIME型は、タイムゾーン非対応であり、格納時の値がそのまま保持される。</u><br>
<br><br>

== 日付・時刻関数を使用する時の注意 ==
==== インデックスと関数適用 ====
WHERE句で日付カラムに関数を適用すると、インデックスが無効化される。<br>
<br>
非効率な例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- インデックスが使用されない
 SELECT * FROM sales WHERE YEAR(sale_date) = 2024;
 SELECT * FROM sales WHERE DATE(order_datetime) = '2024-02-15';
 </syntaxhighlight>
<br>
効率的な書き換え例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- インデックスが使用される
 SELECT * FROM sales
 WHERE sale_date BETWEEN '2024-01-01' AND '2024-12-31';
 
 SELECT * FROM sales
 WHERE order_datetime >= '2024-02-15' AND order_datetime < '2024-02-16';
 </syntaxhighlight>
<br>
カラムに関数を適用せず、範囲検索に変換することで、インデックスが有効化される。<br>
<br>
==== 2038年問題 ====
TIMESTAMP型は、UNIXタイムスタンプの制約により、2038-01-19 03:14:07 UTC以降の日付を扱えない。<br>
<br>
2038年以降の日付を扱う場合は、DATETIME型の使用を推奨する。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- TIMESTAMP型の限界
 SELECT FROM_UNIXTIME(2147483647);
 -- 出力: 2038-01-19 03:14:07
 
 -- DATETIME型は2038年以降も対応
 SELECT '2050-01-01 00:00:00' + INTERVAL 10 YEAR;
 -- 出力: 2060-01-01 00:00:00
 </syntaxhighlight>
<br>
==== NULL値の扱い ====
ほとんどの日付関数は、NULL引数に対してNULLを返す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT YEAR(NULL);
 -- 出力: NULL
 
 SELECT DATE_ADD(NULL, INTERVAL 1 DAY);
 -- 出力: NULL
 
 SELECT DATEDIFF('2024-02-15', NULL);
 -- 出力: NULL
 </syntaxhighlight>
<br>
NULL値を含む可能性がある場合は、<code>COALESCE()</code> 関数 や <code>IFNULL()</code> 関数で対処する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT YEAR(COALESCE(event_date, CURDATE())) FROM events;
 </syntaxhighlight>
<br>
==== MySQL 8.0での新機能 ====
MySQL 8.0では、以下に示す日付・時刻関連の改善が行われている。<br>
<br>
* 小数秒精度のサポート拡張
*: DATETIME、TIMESTAMP、TIME型でfsp 0-6をサポート
* TIMESTAMP型の自動更新改善
*: DEFAULT CURRENT_TIMESTAMP、ON UPDATE CURRENT_TIMESTAMPの動作改善
* タイムゾーン変換の強化
*: タイムゾーンテーブルの更新、CONVERT_TZ()のパフォーマンス改善
* ウィンドウ関数との組み合わせ
*: LAG()、LEAD()等のウィンドウ関数で日付・時刻値を扱える
<br>
MySQL 8.0でのウインドウ関数の使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT
    order_date,
    order_amount,
    LAG(order_date) OVER (ORDER BY order_date) AS prev_order_date,
    DATEDIFF(order_date, LAG(order_date) OVER (ORDER BY order_date)) AS days_since_last_order
 FROM orders;
 </syntaxhighlight>
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,MySQL,SQL,Database,Date,Time,DateTime,Timestamp,DateFormat,DateAdd,DateDiff,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - 電子回路とSUSE Linuxに関する情報 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]