MySQL - 日付・時刻関数

📢 Webサイト閉鎖と移転のお知らせ

このWebサイトは2026年9月に閉鎖いたします。

移転先はhttps://mochiu.net/index.php?title=MySQL - 日付・時刻関数です。

新しい記事は移転先で追加しております。（旧サイトでは記事を追加しておりません）

概要

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の日付・時刻データ型を以下に示す。

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) の指定
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秒

フォーマット指定子一覧

主要なフォーマット指定子を以下に示す。

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

複数の指定子を組み合わせて、任意のフォーマットを作成できる。

日本語形式の日付フォーマット例を以下に示す。

 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句で使用できる単位を以下に示す。

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)

戻り値は、 ${\mbox{date1}}-{\mbox{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)

戻り値は、 ${\mbox{datetime2}}-{\mbox{datetime1}}$ の差分である。
第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)

戻り値は、 ${\mbox{expr1}}-{\mbox{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;