DATE
| TIME
| TIMESTAMP
| DATETIME
| YEAR
*DATE [#w9fa72d8]
mysql> create table d ( d date );
Query OK, 0 rows affected (0.06 sec)
mysql> insert into d values('2007-08-06');
Query OK, 1 row affected (0.02 sec)
mysql> select * from d;
+------------+
| d |
+------------+
| 2007-08-06 |
+------------+
1 row in set (0.00 sec)
mysql> insert into d values(NULL);
Query OK, 1 row affected (0.03 sec)
mysql> select * from d;
+------------+
| d |
+------------+
| 2007-08-06 |
| NULL |
+------------+
2 rows in set (0.00 sec)
mysql> select * from d where d='2007-08-06';
+------------+
| d |
+------------+
| 2007-08-06 |
+------------+
1 row in set (0.00 sec)
mysql> select * from d where d='2007/08/06';
+------------+
| d |
+------------+
| 2007-08-06 |
+------------+
1 row in set (0.00 sec)
-日付の形式は「年-月-日」や「年/月/日」を使える。
-区切り文字は何でも良いようだ。(区切り文字を保存しているわけでない。あくまでも保存しているのは年月日)
mysql> insert into d values('2010@08@30');
Query OK, 1 row affected (0.03 sec)
mysql> select * from d where d = '2010|08:30';
+------------+
| d |
+------------+
| 2010-08-30 |
+------------+
1 row in set (0.00 sec)
*TIME [#tf0eeb6d]
MySQLでは、TIME型は時刻を格納するだけではなく~
時間の間隔を保存できるように-838:59:59～838:59:59の範囲の時間を保存できます。~
mysql> create table t ( t time );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into t values('10:10:10');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t;
+----------+
| t |
+----------+
| 10:10:10 |
+----------+
1 row in set (0.00 sec)
mysql> insert into t values('-838:59:59');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t values('838:59:59');
Query OK, 1 row affected (0.03 sec)
mysql> select * from t;
+------------+
| t |
+------------+
| 10:10:10 |
| -838:59:59 |
| 838:59:59 |
+------------+
3 rows in set (0.01 sec)
-また、時間を日数換算で指定することもできます。但し指定できる範囲は0から33日までです。
mysql> insert into t values('15 5:30:21');
Query OK, 1 row affected (0.03 sec)
mysql> select * from t;
+------------+
| t |
+------------+
| 10:10:10 |
| -838:59:59 |
| 838:59:59 |
| 365:30:21 | ←これが１５日（３６０時間）と５時間３０分２１秒
+------------+
6 rows in set (0.00 sec)
*TIMESTAMP [#xa619097]
mysql> create table ts ( i int, ts timestamp );
Query OK, 0 rows affected (0.06 sec)
mysql> insert into ts(i) values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from ts;
+------+---------------------+
| i | ts |
+------+---------------------+
| 1 | 2007-08-07 16:09:02 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> create table ts2 ( i int, ts1 timestamp, ts2 timestamp );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into ts2(i) values(1);
Query OK, 1 row affected (0.03 sec)
mysql> select * from ts2;
+------+---------------------+---------------------+
| i | ts1 | ts2 |
+------+---------------------+---------------------+
| 1 | 2007-08-07 16:10:47 | 0000-00-00 00:00:00 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)
-２つのtimestamp型が存在した場合は、１つめのフィールドだけに、挿入時、更新時にセットされます。
*DATETIME [#lde9a930]
mysql> create table dt (dt datetime );
Query OK, 0 rows affected (0.09 sec)
mysql> insert into dt values('2007-08-06 16:15:30');
Query OK, 1 row affected (0.03 sec)
mysql> select * from dt;
+---------------------+
| dt |
+---------------------+
| 2007-08-06 16:15:30 |
+---------------------+
1 row in set (0.00 sec)
*YEAR [#a5fbae73]
mysql> create table y ( y year );
Query OK, 0 rows affected (0.09 sec)
mysql> insert into y values('2007-08-30');
ERROR 1265 (01000): Data truncated for column 'y' at row 1
mysql> insert into y values('2007');
Query OK, 1 row affected (0.03 sec)
mysql> insert into y values('1968');
Query OK, 1 row affected (0.03 sec)
mysql> insert into y values('-1');
ERROR 1264 (22003): Out of range value adjusted for column 'y' at row 1
mysql> select * from y;
+------+
| y |
+------+
| 2007 |
| 1968 |
+------+
2 rows in set (0.00 sec)
