まとめ

MySQLの日付型の扱い方や機能をまとめてみました

MyBatisとMySQLで日付型を利用する時に、どのように扱えばいいのかをまとめてみました。

前提

  • MySQL 5.6.13 ※現在の最新は5.7ですが、5.6を対象とします。

MySQLの日付型の概要

MySQLの日付型は現在５種類存在します。

  • DATE
  • DATETIME
  • TIMESTAMP
  • TIME
  • YEAR

詳しくは公式サイトの11.3. Date and Time Typesに載っています。翻訳版だとMySQL5.1用ですが、10.3. 日付と時刻タイプが参考になります。

日付は柔軟に入力できます。例えば’2013-01-01’という形式でも’2013/01/01’という形式でも’20130101’という形式でもOKです。ただし曖昧に解釈できてしまうものはエラーになります。例えば’2013111’は1月11日なのか11月1日なのか分からないため、エラーになります。

DATE型

形式：‘YYYY-MM-DD’ 値の範囲：‘1000-01-01’ to ‘9999-12-31’ 時、分、秒などは扱えません。

DATETIME型

形式：‘YYYY-MM-DD HH:MM:SS[.fraction]’ 値の範囲：‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’ ※[.fraction]は小数部(マイクロ秒)です。詳しくは後述します。

TIMESTAMP型

形式：‘YYYY-MM-DD HH:MM:SS[.fraction]’ 値の範囲：‘1970-01-01 00:00:01.000000’ UTC to ‘2038-01-19 03:14:07.999999’ ※[.fraction]は小数部(マイクロ秒)です。詳しくは後述します。

TIMESTAMPはDATETIMEと同じに見えますが、いくつか違いがあります。TIMESTAMPはどのように違うのかをまとめてみました。

  • 値の範囲 TIMESTAMPの方が表現範囲が小さいです。
  • データサイズ TIMESTAMPの方が小さいです。DATETIMEは5byte～8byteですが、TIMESTAMPは4byte～7byteです。可変なのは小数部があるからです。
  • UTC変換の有無 TIMESTAMPはUTCに変換した日付が格納されます。取り出すときは逆変換されます。
  • 行を操作した日付の自動格納の有無 TIMESTAMPは行データを操作したとき(設定次第では)、値が自動格納・自動更新されます。

詳しくは後述します。

TIME型

形式：‘HH:MM:SS[.fraction]’ 値の範囲：‘-838:59:59.000000’ to ‘838:59:59.999999’ ※[.fraction]は小数部(マイクロ秒)です。詳しくは後述します。

期間を表すためには良いデータ構造だと思います。例えばユーザの連続接続時間の履歴とか。ある海外旅行移動ルートの所要時間とか。２４時間を超えるシーンで活躍できそうです。

しかしアプリケーションとの連携を考えると少し微妙な気がします。例えばC#にはTimeSpan構造体というTIME型に合致したものがありますが、DBコネクション経由でTimeSpanオブジェクトを直に受け渡しできるなら良いと思います。できないのであれば、別の一般的な型を利用した方が良い気もします。そのあたりはO/Rマッパーや独自の中間層次第という気もしますが。

YEAR型

形式：‘YYYY’ 値の範囲：‘1901’ to ‘2155’

小数部（マイクロ秒）の表現と利用方法

上記で[.fraction]とした部分があります。これは小数部です。.5とすれば500ミリ秒を表現できますし、.000001とすれば、1マイクロ秒を表現できます。

これはMySQL5.6.4から利用できます。利用方法は簡単で、型の後ろに(桁数)とするだけです。実際にやってみます。

create table FRACTION_SAMPLE(F0 datetime, F1 datetime(1), F6 datetime(6));
insert into FRACTION_SAMPLE values('2013-01-01 12:30:00.123456', '2013-01-01 12:30:00.123456', '2013-01-01 12:30:00.123456');
select * from FRACTION_SAMPLE;

+---------------------+-----------------------+----------------------------+
| F0                  | F1                    | F6                         |
+---------------------+-----------------------+----------------------------+
| 2013-01-01 12:30:00 | 2013-01-01 12:30:00.1 | 2013-01-01 12:30:00.123456 |
+---------------------+-----------------------+----------------------------+
1 row in set (0.00 sec)

小数部も格納できました。

各日付型のサイズ

MySQL5.6のマニュアルから引用(翻訳)します。

データ型MySQL5.6.4未満の必要な領域MySQL 5.6.4以降の必要な領域
YEAR1 byte1 byte
DATE3 bytes3 bytes
TIME3 bytes3 bytes + 小数部の領域
DATETIME8 bytes5 bytes + 小数部の領域
TIMESTAMP4 bytes4 bytes + 小数部の領域

小数部の領域、とありますが、これは0～3 bytesです。小数部が1, 2桁なら1 byte、3, 4桁なら2 bytes、5, 6桁なら3bytesです。

TIMESTAMP型の値の範囲

TIMESTAMPはいわゆるUNIX時間、time_tで、1970年から始まる日付であり、4byteです。そのため、2038年までしか格納できません。MySQLだけではありませんが、2038年問題というものです。利用する時は気を付ける必要があります。

TIMESTAMP型のデータ保持形式(UTC)

マニュアルにTIMESTAMPは内部でUTCで持つと書かれています。これがどういうことなのか確かめてみます。

まずは以下のコマンドを実行してみます。

create table TIMESTAMP_SAMPLE(DT datetime, TS timestamp);
insert into TIMESTAMP_SAMPLE values('2013-01-01 12:30:00', '2013-01-01 12:30:00');
select * from TIMESTAMP_SAMPLE;

+---------------------+---------------------+
| DT                  | TS                  |
+---------------------+---------------------+
| 2013-01-01 12:30:00 | 2013-01-01 12:30:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

DATETIMEは比較用です。２つとも同じ時間が表示されました。

２つとも同じ時間が表示されていますが、内部値は異なります。DT列にはまさしくそのままの値12時30分が格納されていますが、TS列にはUTC変換された時間が格納されています。日本は+9時間ですので、UTCで3時30分の値が格納されているはずです。上記の表示が12時30分なのは、取り出すときに現在のタイムゾーンに変換される(+9時間される)ためです。

本当にそうなんでしょうか？タイムゾーンを変更して確かめます。

set time_zone = '+00:00';
select * from TIMESTAMP_SAMPLE;

+---------------------+---------------------+
| DT                  | TS                  |
+---------------------+---------------------+
| 2013-01-01 12:30:00 | 2013-01-01 03:30:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

TS列が3:30で表示されました。

TIMESTAMP型の日付の自動格納

よく、「レコードの最終更新日を格納しておく列を用意する」という設計を見かけます。これにはTIMESTAMP型が適しているかも知れません。

ただし、これには色々と注意が必要です。もし、１テーブルにTIMESTAMP列を２つ作成する場合は注意が必要です。適当なワードでGoogle検索すると注意してね、という情報が出てきますが、その中のいくつかの情報は古く現在では間違った情報になっています。TIMESTAMP列を２つ作成する件については以降の部分で、説明します。

まずは自動更新をざっくり確かめます。上記のTIMESTAMP_SAMPLEテーブルを利用して、以下のようなinsert文を発行してみます。

insert into TIMESTAMP_SAMPLE(DT) values('2013-02-02 02:02:02');

+---------------------+---------------------+
| DT                  | TS                  |
+---------------------+---------------------+
| 2013-02-02 02:02:02 | 2013-10-13 00:14:41 |
+---------------------+---------------------+
1 row in set (0.00 sec)

TS列には何も指定しませんでしたが、現在日時が挿入されました。

小数部ありのTIMESTAMP型の場合、マイクロ秒まで入ります。

alter table TIMESTAMP_SAMPLE modify column TS timestamp(6);
insert into TIMESTAMP_SAMPLE(DT) values('2013-03-03 03:03:03');

+---------------------+----------------------------+
| DT                  | TS                         |
+---------------------+----------------------------+
| 2013-03-03 03:03:03 | 2013-10-13 00:23:29.100492 |
+---------------------+----------------------------+
1 row in set (0.00 sec)

レコードをUPDATEしたときも、TS列は変わります。

update TIMESTAMP_SAMPLE set DT = '2013-04-04 04:04:04';

+---------------------+----------------------------+
| DT                  | TS                         |
+---------------------+----------------------------+
| 2013-04-04 04:04:04 | 2013-10-13 00:27:18.703836 |
+---------------------+----------------------------+
1 row in set (0.00 sec)

TS列にNULLを入れようと思っても、それはできません。

insert into TIMESTAMP_SAMPLE values('2013-05-05 05:05:05', NULL);

+---------------------+----------------------------+
| DT                  | TS                         |
+---------------------+----------------------------+
| 2013-05-05 05:05:05 | 2013-10-13 00:30:18.039102 |
+---------------------+----------------------------+
1 row in set (0.00 sec)

NULLの場合は日付が入ってしまいます。もちろん、明示的に日付を指定してTS列に入れた場合は、更新日時ではなく、指定した日付が入ります。

TIMESTAMP列が２つある場合も確かめます。

alter table TIMESTAMP_SAMPLE add column TS2 TIMESTAMP(6);
insert into TIMESTAMP_SAMPLE(DT) values('2013-06-06 06:06:06');

+---------------------+----------------------------+----------------------------+
| DT                  | TS                         | TS2                        |
+---------------------+----------------------------+----------------------------+
| 2013-06-06 06:06:06 | 2013-10-13 00:49:00.464034 | 0000-00-00 00:00:00.000000 |
+---------------------+----------------------------+----------------------------+
1 row in set (0.00 sec)

TS列だけに現在日時が入り、TS2列は現在日時が入っていません。TS列が初期作成日時列でTS2列が更新日時列だった場合正しく動きません。ではどうするかというと、以下のようにすることで上手く動きます。

create table TIMESTAMP_SAMPLE2(DT DATETIME, TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP, TS2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
insert into TIMESTAMP_SAMPLE2(DT) values('2013-07-07 07:07:07');
update TIMESTAMP_SAMPLE2 set DT = '2013-08-08 08:08:08';
select * from TIMESTAMP_SAMPLE2;

+---------------------+---------------------+---------------------+
| DT                  | TS                  | TS2                 |
+---------------------+---------------------+---------------------+
| 2013-08-08 08:08:08 | 2013-10-13 01:03:09 | 2013-10-13 01:03:21 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

TS列は始めにinsertしたときの日時、TS2列は最後に更新した日時になりました。ポイントはON UPDATEの有無です。

今回の場合、TS列(初期作成日時列)はタイムスタンプという意味ではデータ型=TIMESTAMPで正しいのですが、格納するデータの特性と型の特徴を考えると、正しくはDATETIME型でしょう。ただし、最終更新日時とデータ型が違うのも少し気になるので、TIMESTAMP型も間違っているとは言えないと思います。

以上で終了です。大半がTIMESTAMP型の説明になってしまいました。TIMESTAMP型は少し難しいので、扱いは慎重にならなければいけないですね。