MySQL数据库中有字段类型是日期、时间的插入语句的写法处理

MySQL数据库中，与时间有关的类型有 DATE，TIME，TIMESTAMP：

时间戳  mysql> create table ts(f1 TIMESTAMP);
Query OK, 0 rows affected (0.13 sec)

mysql> desc date;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| f1    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+

具体范围如下

mysql> insert into tsvalues(19700101080001);
Query OK, 1 row affected (0.20 sec)

mysql> insert into ts values(20371231235959);
Query OK, 1 row affected (0.19 sec)

mysql> select * from ts ;
+---------------------+
| f1                  |
+---------------------+
| 1970-01-01 08:00:01 |
| 2037-12-31 23:59:59 |
+---------------------+
2 rows in set (0.00 sec)

格式

yyyymmddhhmmss、yymmddhhmmss、yyyymmdd或yymmdd格式

mysql> insert into ts values(19700101080001);

mysql> insert into ts values(19700102);

mysql> insert into ts values(371231);

mysql> insert into ts values(1231);

自动补上0

+---------------------+
| f1                  |
+---------------------+
| 1970-01-01 08:00:01 |
| 2037-12-31 23:59:59 |
| 1970-01-02 00:00:00 |
| 2037-01-02 00:00:00 |
| 2037-12-31 00:00:00 |

+---------------------+

time 类型
mysql> create table  shijian(f1 time);
Query OK, 0 rows affected (0.39 sec)

mysql> desc shijian;

范围从 -8385959 到 8385959
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| f1    | time | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+

mysql> insert into shijian values(-8385959);
Query OK, 1 row affected (0.08 sec)

mysql> insert into shijian values(8385959);
Query OK, 1 row affected (0.13 sec)

不足7位数字就从右到左赋值 没有就赋0

mysql> insert into shijian values(159);

+------------+
| f1         |
+------------+
| 00:01:59   |
| 838:59:59  |
| -838:59:59 |
+------------+

DATETIME类型 范围就是下面的最大最小值

mysql> desc dt;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| f1    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

mysql> create table dt(f1 DATETIME);

mysql> insert into dt value(10000101);

mysql> insert into dt value(99991231);

mysql> insert into dt values(10000101000000);
Query OK, 1 row affected (0.14 sec)

mysql> insert into dt value(99991231235959);
Query OK, 1 row affected (0.17 sec)

+---------------------+
| f1                  |
+---------------------+
| 1000-01-01 00:00:00 |
| 9999-12-31 00:00:00 |
| 9999-12-31 00:00:00 |
| 9999-12-31 23:59:59 |
| 1000-01-01 00:00:00 |
+---------------------+

 

日期

mysql> create table riqi(f1 date);
Query OK, 0 rows affected (0.11 sec)

mysql> desc riqi;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| f1    | date | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+

 mysql> insert into riqi values(10000101);

 mysql> insert into riqi values(99991231);

+------------+
| f1         |
+------------+
| 1000-01-01 |
| 9999-12-31 |
+------------+

年

mysql> create table nian(f1 year);
Query OK, 0 rows affected (0.28 sec)

mysql> desc nian;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| f1    | year(4) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

 

mysql> insert into nian values(1970);
mysql> insert into nian values(2155);

可以使用2位

70以上(包括70)是2000年以前

70以下是2000以后

mysql> insert into nian values(69);

mysql> insert into nian values(70);

mysql> insert into nian values(20);
mysql> insert into nian values(80);
mysql> select * from nian;
+------+
| f1   |
+------+
| 1970 |
| 2155 |
| 1980 |
| 2020 |
| 2069 |
| 1970 |
+------+