-
创建一个datetime表
> create table date_time(time datetime);
> desc date_time;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| time | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+ -
输入"10000-10-10 00:00:00"进行测试
> insert into date_time values("10000-10-10 00:00:00");
ERROR 1292 (22007): Incorrect datetime value: '10000-10-10 00:00:00' for column 'time' at row 1 # 报错 -
输入”23-01-02 12:23:56"测试
> insert into date_time values("23-01-02 12:23:56");
> insert into date_time values("79-01-02 12:23:56");
> select * from date_time;
+---------------------+
| time |
+---------------------+
| 2023-01-02 12:23:56 |
| 1979-01-02 12:23:56 |
+---------------------+会和date一样,'00'-'69'会转换为2000-2069,'70'-'99'会转换为1970-1999
-
测试年份输入三位或者一位
> insert into date_time values("179-01-02 12:23:56");
> insert into date_time values("9-01-02 12:23:56");
> select * from date_time;
+---------------------+
| time |
+---------------------+
| 2023-01-02 12:23:56 |
| 1979-01-02 12:23:56 |
| 0179-01-02 12:23:56 |
| 0009-01-02 12:23:56 |
+---------------------+可以存储进去,但会自动在前面补足0,使年份变为4位
-
输入年份格式为”00YY“
> insert into date_time values("0079-01-02 12:23:56");
> select * from date_time;
+---------------------+
| time |
+---------------------+
| 2023-01-02 12:23:56 |
| 1979-01-02 12:23:56 |
| 0179-01-02 12:23:56 |
| 0009-01-02 12:23:56 |
| 0079-01-02 12:23:56 | # 可以存储
+---------------------+ -
最后测试极限时间
> insert into date_time values("0000-00-00 00:00:00");
> insert into date_time values("9999-12-31 23:59:59");
> select * from date_time;
+---------------------+
| time |
+---------------------+
| 2023-01-02 12:23:56 |
| 1979-01-02 12:23:56 |
| 0179-01-02 12:23:56 |
| 0009-01-02 12:23:56 |
| 0079-01-02 12:23:56 |
| 0000-00-00 00:00:00 |
| 9999-12-31 23:59:59 |
+---------------------+
总结:
- datetime类型范围为”0000-00-00 00:00:00“~”9999-12-31 23:59:59“;
- 要存储年份为两位数的需要在前面补足”00“,不然会按照date类型进行转换('00'-'69'会转换为2000-2069,'70'-'99'会转换为1970-1999);