🚀

MySQLの時間データ型 詳解

2023/09/13に公開

MySQLの時間データ型

MySQLで時系列データを格納するために5つの異なるデータ型が使用できます。
表現したい時間の詳細度(年、日付、時刻)に応じて使用できるデータ型が異なります。

YEAR

年のみを格納する必要がある場合、YEARが最もコンパクトな方法です。1901年から2155年までの年を格納できます。
このタイプはあまり一般的には使用されません。

DATE

年月日のみを格納する必要がある場合、DATE型のカラムが最適です。DATEは3バイトのデータ型で、1,000年から9,999年までの幅広いデータを格納できます。

TIME

TIMEデータ型は、時間、分、秒を格納するために使用されます。24時間を超えるデータを格納できるため、時間間隔を格納するのに役立ちます。
このタイプは、時間、分、秒で表される10日間の範囲を格納するのに役立ちますが、一般的には使用されません。

DATETIMEとTIMESTAMP

年月日と時間、分、秒を格納する必要がある場合、DATETIMEとTIMESTAMPの2つの選択肢があります。以下にDATETIME、TIMESTAMPの特徴について表にまとめます。

データ型 格納サイズ データの範囲 タイムゾーン
DATETIME 8バイト 1001年~9999年 タイムゾーンを考慮しない
TIMESTAMP 4バイト 1970年〜2038年 タイムゾーンを考慮する

2038年までのデータを格納したいならTIMESTAMPを使用するのがデータサイズ的に効率が良いです。

コンマ秒の格納

MySQLのTIME,DATETIME,TIMESTAMPはマイクロ秒(小数第6位)までの精度で後続の小数秒部分を含めることができます。
小数秒部を含むカラムを定義するには、DATETIME(2)のように型名(精度) の構文を使用します。
指定された精度を超える部分は丸めでテーブルに挿入されます。

小数第2位までの小数秒を保存するカラムを定義してみましょう。

CREATE TABLE times ( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
INSERT INTO times VALUES ('11:34:08.589', '2023-09-12 14:51:04.888', '2021-12-04 17:51:04.777');

このカラムを表示します。

SELECT c1, c2, c3 FROM times;

| c1           | c2                     | c3                     |
|--------------|------------------------|------------------------|
| 11:34:08.59  | 2023-09-12 14:51:04.89 | 2021-12-04 17:51:04.78 |

挿入した数値が丸められていることが確認できます。

まとめ

MySQLで時系列データを格納する際には、要件に基づいて適切なデータ型を選択することが重要です。
DATE、DATETIME、TIMESTAMPデータ型は最も一般的に使用されます。
DATETIMEとTIMESTAMPの選択時には、ストレージサイズ、必要なデータの範囲、タイムゾーンの扱いを考慮する必要があります。

参考資料

https://dev.mysql.com/doc/refman/8.0/ja/datetime.html
https://dev.mysql.com/doc/refman/8.0/ja/fractional-seconds.html
https://planetscale.com/learn/courses/mysql-for-developers/schema/dates

関連リンク

MySQLの整数値型 詳解
MySQLの小数値型 詳解
MySQLの文字列型 CHAR、VARCHAR 詳解
MySQLのバイナリデータ型 BINARY, VARBINARY 詳解
MySQLのTEXT、BLOB 詳解
MySQLのENUM型 詳解
MySQLの時間データ型 詳解
MySQLのJSON型 詳解
MySQL - innodbにおける各データ型のサイズまとめ

Discussion