日付型と時刻型(DATE, TIME, DATETIME, TIMESTAMP, YEAR)

MySQL で利用可能なデータ型の中で日付型と時刻型(DATE, TIME, DATETIME, TIMESTAMP, YEAR)の使い方について解説します。

(Last modified: )

日付型と時刻型のデータ型一覧

日付型と時刻型のデータ型として次の 5 つが用意されています。

DATE
日付
'1000-01-01' から '9999-12-31'
フォーマット : 'YYYY-MM-DD'

DATETIME[(fsp)]
日付と時間の組み合わせ
'1000-01-01 00:00:00.000000' から '9999-12-31 23:59:59.999999'
フォーマット : 'YYYY-MM-DD HH:MM:SS[.fraction]'

TIMESTAMP[(fsp)]
タイムスタンプ
TIMESTAMP 値は、エポック ('1970-01-01 00:00:00' UTC) からの秒数
'1970-01-01 00:00:01.000000' UTC から '2038-01-19 03:14:07.999999' UTC
フォーマット : 

TIME[(fsp)]
時間
'-838:59:59.000000' から '838:59:59.000000'
フォーマット : 'HH:MM:SS[.fraction]' 

YEAR[(2|4)]
2 桁または 4 桁の形式の年
4 桁の形式では、値は 1901 から 2155 と 0000
2 桁の形式では、値は 70 から 69 ( 1970 から 2069 に相当)
フォーマット : YYYY または YY 

DATETIME 型、 TIMESTAMP 型、 TIME 型では引数に秒の小数部の桁数を指定することができます。この値は 0 から 6 までの範囲で指定可能です。省略した場合のデフォルトの値は 0 です。

それではそれぞれのデータ型について詳しく確認してみます。

DATE型

DATE 型は日付を格納するのに使用するデータ型です。日付を文字列として入力する場合、 'YYYY-MM-DD' が基本となるフォーマットですが、次のような入力方法も許可されています。

Format         Sample
---------------------------
'YYYY-MM-DD'   '2019-09-07'
'YY-MM-DD'     '19-09-07'
'YYYYMMDD'     '20190907'
'YYMMDD'       '190907'

また区切り文字は '-' だけではなく次のような文字を使うこともできます。

Format         Sample
---------------------------
'YYYY-MM-DD'   '2019-09-07'
'YYYY.MM.DD'   '2019.09.07'
'YYYY/MM/DD'   '2019/09/07'
'YYYY@MM@DD'   '2019@09@07'

日付に区切り文字が使われている場合、 1 月から 9 月、そして 1 日から 9 日については 2 桁で入力する必要はありません。

Format         Sample
---------------------------
'YYYY-M-D'     '2019-9-7'

日付は数値として入力することもできます。

Format         Sample
---------------------------
YYYYMMDD       20190907
YYMMDD         190907

なお年が 2 桁で入力された場合は次のルールによって 4 桁の年に変換します。

70-99 の範囲の年の値は 1970-1999
00-69 の範囲の年の値は 2000-2069

それでは DATE 型のカラムを持つテーブルを作成します。

create table datetest(dt date);

DATE型(1)

いろいろなフォーマットでデータを追加してみます。

insert into datetest values('2019-04-17');
insert into datetest values('18-12-5');
insert into datetest values('97-06-24');
insert into datetest values('20190208');
insert into datetest values(150401);

DATE型(2)

追加したデータを SELECT 文で取得します。

select * from datetest;

DATE型(3)

いずれのフォーマットで入力しても意図した通りにデータが登録されていることが確認できました。

TIME型

TIME 型は時刻を格納するのに使用するデータ型です。時刻を文字列として入力する場合、 'HH:MM:SS' が基本となるフォーマットですが、次のような入力方法も許可されています。

Format         Sample
---------------------------
'HH:MM:SS'     '15:25:07'
'HH:MM'        '15:25'
'HHMMSS'       '152507'
'MMSS'         '2507'
'SS'           '07'
'D HH:MM:SS'   '2 15:25:07'
'D HH:MM'      '2 15:25'
'D HH''        '2 15'

'MMSS' で入力した場合は分と秒になり 'SS' で入力した場合は秒となりますが、 'HH:MM' のように区切り文字がある場合は時と分になりますので注意して下さい。

'D' は日にちを表します。 'D' 付きの場合は D×24 が時に加算されて表示されます。

時間に区切り文字が使われている場合、 0 秒から 9 秒、 0 分から 9 分、 0 時から 9 時については 2 桁で入力する必要はありません。

Format         Sample
---------------------------
'HH:M:S'       '15:3:7'

時間は数値として入力することもできます。

Format         Sample
---------------------------
HHMMSS         152507
MMSS           2507
SS             07

小数秒を入力する場合は次のように入力します。 fraction は最大 6 桁です。

Format                 Sample
-------------------------------------
'HH:MM:SS.fraction'    '15:25:07.205'
'HHMMSS.fraction'      '152507.205'
'D HH:MM:SS.fraction'  '2 15:25:07.205'
HHMMSS.fraction        152507.205

それでは TIME 型のカラムを持つテーブルを作成します。

create table timetest(t time);

TIME型(1)

いろいろなフォーマットでデータを追加してみます。

insert into timetest values('15:24:39');
insert into timetest values('08:14');
insert into timetest values('220732');
insert into timetest values('1743');
insert into timetest values('3 19:02:51');

TIME型(2)

追加したデータを SELECT 文で取得します。

select * from timetest;

TIME型(3)

いずれのフォーマットで入力しても意図した通りにデータが登録されていることが確認できました。

-- --

今度は秒の小数部の桁数を指定した TIME 型のカラムを持つテーブルを作成します。

create table timetest(t time(3));

TIME型(4)

いろいろなフォーマットでデータを追加してみます。

insert into timetest values('15:24:39.304');
insert into timetest values(210328.521);

TIME型(5)

追加したデータを SELECT 文で取得します。

select * from timetest;

TIME型(6)

小数秒を持つデータについていずれのフォーマットで入力しても意図した通りにデータが登録されていることが確認できました。

DATETIME型

DATETIME 型は日付と時刻を格納するのに使用するデータ型です。日付と時刻を文字列として入力する場合、 'YYYY-MM-DD HH:MM:SS' が基本となるフォーマットですが、次のような入力方法も許可されています。

Format                Sample
-----------------------------------------------
'YYYY-MM-DD HH:MM:SS' '2019-10-04 15:25:07'
'YY-MM-DD HH:MM:SS'   '19-10-04 15:25:07'
'YYYYMMDDHHMMSS'      '20191004152507'
'YYMMDDHHMMSS'        '191004152507'

また区切り文字は '-' だけではなく次のような文字を使うこともできます。

Format                 Sample
-----------------------------------------------
'YYYY-MM-DD HH:MM:SS'  '2019-10-04 15:25:07'
'YYYY.MM.DD HH+MM+SS'  '2019.10.04 15+25+07'
'YYYY/MM/DD HH*MM*SS'  '2019/10/04 15*25*07'
'YYYY@MM@DD HH^MM^SS'  '2019@10@04 15^25^07'

日付や時刻に区切り文字が使われている場合、 1 桁の数値を 2 桁で入力する必要はありません。

Format                 Sample
-----------------------------------------------
'YYYY-M-D H:M:S'         '2009-3-7 8:2:9'

数値として入力することもできます。

Format                 Sample
-----------------------------------------------
YYYYMMDDHHMMSS         20191004152507
YYMMDDHHMMSS           191004152507

それでは DATETIME 型のカラムを持つテーブルを作成します。

create table datetest(dt datetime);

DATETIME型(1)

いろいろなフォーマットでデータを追加してみます。

insert into datetest values('2019-05-02 12:48:35');
insert into datetest values('97-12-24 08:12:13');
insert into datetest values(20181126140235);
insert into datetest values('2019/7/4 3+15+9');

DATETIME型(2)

追加したデータを SELECT 文で取得します。

select * from datetest;

DATETIME型(3)

いずれのフォーマットで入力しても意図した通りにデータが登録されていることが確認できました。なお秒の小数部の桁数を指定する場合は TIME 型を参照されてください。

TIMESTAMP型

TIMESTAMP 型は日付と時刻を格納するのに使用するデータ型です。フォーマットは DATETIME 型とほぼ同じですが、扱える値の範囲が少ないこと、そして利用する目的が異なります。詳しくは別のページで解説しますが、ここでは簡単に TIMESTAMP 型のカラムを持つテーブルを作成して試してみます。

create table stamptest(ts timestamp);

TIMESTAMP型(1)

いろいろなフォーマットでデータを追加してみます。

insert into stamptest values('2019-05-02 12:48:35');
insert into stamptest values('2019/7/4 3+15+9');

TIMESTAMP型(2)

追加したデータを SELECT 文で取得します。

select * from stamptest;

TIMESTAMP型(3)

いずれのフォーマットで入力しても意図した通りにデータが登録されていることが確認できました。

YEAR型

YEAR 型は年を格納するのに使用するデータ型です。年を数値として指定する場合、YYYY が基本となるフォーマットですが、次のような入力方法も許可されています。

Format            Sample
----------------------------------
YYYY              2019
YY                19

文字列として入力することもできます。

Format            Sample
----------------------------------
'YYYY'            '2019'
'YY'              '98'

なお年が 2 桁で指定された場合は次のルールによって 4 桁の年に変換します。

70-99 の範囲の年の値は 1970-1999
 1-69 の範囲の年の値は 2001-2069

DATE 型などと異なり 0 を指定すると 2000 にはなりませんので注意が必要です。数字の 0 や文字列の '0' を指定すると 0000 に変換されます。

それでは YEAR 型のカラムを持つテーブルを作成します。

create table yeartest(y year);

YEAR型(1)

いろいろなフォーマットでデータを追加してみます。

insert into yeartest values(2018);
insert into yeartest values(97);
insert into yeartest values('2017');
insert into yeartest values(0);

YEAR型(2)

追加したデータを SELECT 文で取得します。

select * from yeartest;

YEAR型(3)

いずれのフォーマットで入力しても意図した通りにデータが登録されていることが確認できました。

-- --

MySQL で利用可能なデータ型の中で日付型と時刻型の使い方について解説しました。

( Written by Tatsuo Ikura )

Profile
profile_img

著者 / TATSUO IKURA

プログラミングや開発環境構築の解説サイトを運営しています。