概要
MySQLは、日付と時刻を扱うための豊富な関数セットを提供している。
日付・時刻データ型には、DATE、TIME、DATETIME、TIMESTAMP、YEARの5つが存在する。
日付・時刻関数は、以下に示す用途で使用される。
| 用途 | 主な関数 |
|---|---|
| 現在日時の取得 | 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等 |
これらの関数を効果的に使用することで、複雑な日付計算、レポート生成、データ分析を実現できる。
日付・時刻型は、フォーマット、範囲、サイズ、タイムゾーン対応の点で異なる特性を持つ。
TIMESTAMP型は、タイムゾーン対応と自動初期化機能を持つ点で、DATETIME型と異なる。
MySQL 5.6.4以降では、DATETIME、TIMESTAMP、TIME型において、小数秒精度 (fsp : fractional seconds precision) がサポートされている。
小数秒精度は、0 (秒単位) から6 (マイクロ秒単位) まで指定できる。
日付・時刻関数を使用する時は、インデックスの有効性、NULL値の扱い、タイムゾーン設定に注意する必要がある。
特に、WHERE 句で日付カラムに関数を適用すると、インデックスが無効化されるため、範囲検索に変換することが推奨される。
データ型
データ型の比較
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バイト | 非対応 | 非対応 |
DATE型は、日付のみを格納し、時刻部分を持たない。
TIME型は、時刻または経過時間を格納し、負の値をサポートしている。
DATETIME型は、タイムゾーン非対応であり、格納時の値がそのまま保持される。
TIMESTAMP型は、格納時にUTCに変換され、取得時にセッション時刻に変換される。
TIMESTAMP型の範囲は、UNIXタイムスタンプの制約により、2038年問題の影響を受ける。
2038年以降の日付を扱う場合は、DATETIME型の使用を推奨する。
YEAR型は、年のみを格納する。
YEAR(2)型 (2桁年) は、MySQL 8.0で廃止された。
小数秒精度 (fsp)
MySQL 5.6.4以降では、DATETIME、TIMESTAMP、TIME型において、小数秒精度 (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 |
カラム定義での小数秒精度の指定例を以下に示す。
CREATE TABLE events (
event_id INT PRIMARY KEY,
event_datetime DATETIME(3), -- ミリ秒精度
event_timestamp TIMESTAMP(6), -- マイクロ秒精度
event_time TIME(3) -- ミリ秒精度
);
小数秒精度を指定すると、ストレージサイズが増加する。
fsp 1〜2は1バイト、fsp 3〜4は2バイト、fsp 5〜6は3バイトの追加ストレージが必要となる。
現在日時の取得
現在の日付・時刻を取得する関数を以下に示す。
| 関数 | 戻り値 | 説明 |
|---|---|---|
| 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タイムスタンプを返す |
NOW() 関数は、クエリ実行開始時点の日時を返す。
同一クエリ内で複数回呼び出しても、同じ値を返す。
SYSDATE() 関数は、関数実行時点の日時を返す。
同一クエリ内で複数回呼び出すと、異なる値を返す可能性がある。
NOW() と SYSDATE() の違いを示す例を以下に示す。
SELECT NOW(), SLEEP(2), NOW(); -- 同じ値を返す
SELECT SYSDATE(), SLEEP(2), SYSDATE(); -- 2秒の差がある
UTC系関数は、サーバーのタイムゾーン設定に依存せず、常にUTC時刻を返す。
国際的なアプリケーションでは、UTC系関数の使用を推奨する。
小数秒精度を指定する場合は、引数でfsp値を指定する。
SELECT NOW(3); -- ミリ秒精度の現在日時
SELECT CURTIME(6); -- マイクロ秒精度の現在時刻
日付・時刻のフォーマット
DATE_FORMAT / TIME_FORMAT
日付・時刻を文字列に変換する関数を以下に示す。
DATE_FORMAT(date, format) TIME_FORMAT(time, format)
DATE_FORMAT() 関数は、日付または日付時刻値を指定フォーマットで文字列に変換する。
TIME_FORMAT() 関数は、時刻値を指定フォーマットで文字列に変換する。(時刻関連指定子のみ使用可能)
使用例を以下に示す。
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秒
フォーマット指定子一覧
主要なフォーマット指定子を以下に示す。
| 指定子 | 説明 | 例 |
|---|---|---|
| %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 |
複数の指定子を組み合わせて、任意のフォーマットを作成できる。
日本語形式の日付フォーマット例を以下に示す。
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 (%a) %H:%i:%s');
-- 出力例: 2026年02月15日 (Sat) 14:30:45
日付の加算・減算
DATE_ADD / DATE_SUB
日付に指定した間隔を加算・減算する関数を以下に示す。
DATE_ADD(date, INTERVAL expr unit) DATE_SUB(date, INTERVAL expr unit)
ADDDATE() 関数、SUBDATE() 関数は、DATE_ADD()、DATE_SUB()の別名である。
演算子形式でも使用できる。
date + INTERVAL expr unit date - INTERVAL expr unit
使用例を以下に示す。
-- 現在日時に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ヶ月を加算
月末の自動調整が行われる。
例として、1月31日に1ヶ月を加算すると、2月28日 (または2月29日) が返される。
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
INTERVAL単位一覧
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 |
複合単位を使用することで、複数の時間単位を1度に加算できる。
ADDTIME / SUBTIME
TIME 値 または DATETIME 値に時間を加算・減算する関数を以下に示す。
ADDTIME(expr1, expr2) SUBTIME(expr1, expr2)
第1引数は、TIME 型 または DATETIME 型の値である。
第2引数は、加算・減算する時間 (TIME型) である。
使用例を以下に示す。
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
日付の差分計算
DATEDIFF
2つの日付の日数差分を返す関数を以下に示す。
DATEDIFF(date1, date2)
戻り値は、 の日数差分である。
時刻部分は無視される。
使用例を以下に示す。
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
TIMESTAMPDIFF
2つの日付・時刻の差分を指定単位で返す関数を以下に示す。
TIMESTAMPDIFF(unit, datetime1, datetime2)
戻り値は、 の差分である。
第1引数は、MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEARのいずれかである。
使用例を以下に示す。
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
TIMESTAMPDIFF() 関数は、引数の順序が DATEDIFF() 関数と逆であることに注意する。
TIMEDIFF
2つの時刻の差分を返す関数を以下に示す。
TIMEDIFF(expr1, expr2)
戻り値は、 の時刻差分 (TIME型) である。
使用例を以下に示す。
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
日付・時刻の部分取得
EXTRACT関数
日付・時刻値から特定の部分を抽出する関数を以下に示す。
EXTRACT(<抽出する日付・時刻の単位> FROM <対象の日付・時刻値>)
<抽出する日付・時刻の単位> は、YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、MICROSECOND等である。
<対象の日付・時刻値> は、DATE型、DATETIME型、TIMESTAMP型である。
使用例を以下に示す。
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
個別の抽出関数
日付・時刻の各部分を抽出する専用関数を以下に示す。
| 関数 | 戻り値 | 説明 |
|---|---|---|
| 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 | 月の最終日 |
使用例を以下に示す。
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 (閏年)
LAST_DAY() 関数は、指定月の最終日を返す。
閏年の2月の場合は、2月29日を返す。
文字列と日付の変換
STR_TO_DATE
文字列を日付・時刻値に変換する関数を以下に示す。
STR_TO_DATE(str, format)
第1引数は、変換する文字列である。
第2引数は、第1引数のフォーマットを指定する。(DATE_FORMAT()と同じ指定子)
使用例を以下に示す。
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
STR_TO_DATE() 関数は、変換に失敗した場合はNULLを返す。
SELECT STR_TO_DATE('invalid date', '%Y-%m-%d');
-- 出力: NULL
暗黙の型変換
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型の場合、文字列が自動的に日付に変換される
ただし、明示的な変換を推奨する。
暗黙の型変換は、予期しない結果を招く可能性がある。
UNIXタイムスタンプ
UNIXタイムスタンプ (1970-01-01 00:00:00 UTCからの秒数) と日付の変換関数を以下に示す。
UNIX_TIMESTAMP([date]) FROM_UNIXTIME(unix_timestamp [, format])
UNIX_TIMESTAMP() 関数は、引数なしの場合は現在のUNIXタイムスタンプ、引数ありの場合は指定日時のUNIXタイムスタンプを返す。
FROM_UNIXTIME() 関数は、UNIXタイムスタンプを日付・時刻値に変換する。
使用例を以下に示す。
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());
-- 出力: 現在の日時
UNIX_TIMESTAMP() 関数は、BIGINT型 (符号あり64ビット整数) を返す。
タイムゾーンの影響を受けるため、注意が必要である。
UNIX_TIMESTAMP() 関数は、セッション時刻をUTCに変換してからタイムスタンプを計算する。
FROM_UNIXTIME() 関数は、UTCタイムスタンプをセッション時刻に変換する。
その他の日付関数
その他の日付・時刻関数を以下に示す。
| 関数 | 説明 | 例 |
|---|---|---|
| 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' |
使用例を以下に示す。
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
GET_FORMAT() 関数の形式名には、EUR (ヨーロッパ)、USA (アメリカ)、JIS (日本)、ISO (国際標準)、INTERNAL (MySQL内部形式) が使用できる。
タイムゾーン
MySQLは、タイムゾーン変換機能を提供している。
タイムゾーン変換関数を以下に示す。
CONVERT_TZ(<変換対象の日付・時刻値>, <変換元のタイムゾーン 例: '+00:00'、'UTC'>, <変換先のタイムゾーン 例: '+09:00'、'Asia/Tokyo'>)
第1引数を、第2引数タイムゾーンから第3引数タイムゾーンに変換する。
使用例を以下に示す。
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から日本時間への変換
タイムゾーン設定は、セッションレベルまたはグローバルレベルで設定できる。
-- セッションタイムゾーンの確認
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';
TIMESTAMP型は、格納時にUTCに変換され、取得時にセッション時刻に自動変換される。
TIMESTAMP型の自動変換例を以下に示す。
-- セッション時刻を日本時間に設定
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
DATETIME型は、タイムゾーン非対応であり、格納時の値がそのまま保持される。
日付・時刻関数を使用する時の注意
インデックスと関数適用
WHERE句で日付カラムに関数を適用すると、インデックスが無効化される。
非効率な例を以下に示す。
-- インデックスが使用されない
SELECT * FROM sales WHERE YEAR(sale_date) = 2024;
SELECT * FROM sales WHERE DATE(order_datetime) = '2024-02-15';
効率的な書き換え例を以下に示す。
-- インデックスが使用される
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';
カラムに関数を適用せず、範囲検索に変換することで、インデックスが有効化される。
2038年問題
TIMESTAMP型は、UNIXタイムスタンプの制約により、2038-01-19 03:14:07 UTC以降の日付を扱えない。
2038年以降の日付を扱う場合は、DATETIME型の使用を推奨する。
-- 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
NULL値の扱い
ほとんどの日付関数は、NULL引数に対してNULLを返す。
SELECT YEAR(NULL);
-- 出力: NULL
SELECT DATE_ADD(NULL, INTERVAL 1 DAY);
-- 出力: NULL
SELECT DATEDIFF('2024-02-15', NULL);
-- 出力: NULL
NULL値を含む可能性がある場合は、COALESCE() 関数 や IFNULL() 関数で対処する。
SELECT YEAR(COALESCE(event_date, CURDATE())) FROM events;
MySQL 8.0での新機能
MySQL 8.0では、以下に示す日付・時刻関連の改善が行われている。
- 小数秒精度のサポート拡張
- DATETIME、TIMESTAMP、TIME型でfsp 0-6をサポート
- TIMESTAMP型の自動更新改善
- DEFAULT CURRENT_TIMESTAMP、ON UPDATE CURRENT_TIMESTAMPの動作改善
- タイムゾーン変換の強化
- タイムゾーンテーブルの更新、CONVERT_TZ()のパフォーマンス改善
- ウィンドウ関数との組み合わせ
- LAG()、LEAD()等のウィンドウ関数で日付・時刻値を扱える
MySQL 8.0でのウインドウ関数の使用例を以下に示す。
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;