MySql 时间日期类型

• date 类型:

  表示日期，格式类似这样: 000-00-00

• time 类型:

  表示时间，格式类似这样: 00:00:00

• datetime 类型:

  表示日期时间，格式类似这样: 0000-00-00 00:00:00

  使用 now() 函数的时候，只有在当时执行的时候会获得当前时间，类似用于创建时间。

• timestamp 类型:

  表示“时间戳”,其实就是一个整数数字，该数字是从是“时间起点”到现在为止的“秒数”。

  “时间起点”是: 1970-1-1 0:0:0

  timestamp 类型的字段，无需插入数据，而是会自动取得当前的日期时间(表示当前时刻)，类似用于修改时间或者最后一次更改内容时间。

  而且，此类型字段会在数据被更新时，也同样自动取得当前的日期时间(表示修改的时刻)。

  特别总结: 它在 insert 或 update 某行数据的时候，能够自动获得当前时间。

• year 类型:

  表示年份，格式为: 0000

mysql> create table timeTest (t_time time, t_date date, t_datetime datetime, t_timestamp timestamp, t_year year);

mysql> desc timeTest;
+-------------+-----------+------+-----+-------------------+-----------------------------+
| Field       | Type      | Null | Key | Default           | Extra                       |
+-------------+-----------+------+-----+-------------------+-----------------------------+
| t_time      | time      | YES  |     | NULL              |                             |
| t_date      | date      | YES  |     | NULL              |                             |
| t_datetime  | datetime  | YES  |     | NULL              |                             |
| t_timestamp | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| t_year      | year(4)   | YES  |     | NULL              |                             |
+-------------+-----------+------+-----+-------------------+-----------------------------+

mysql> insert into timeTest (t_time, t_date, t_datetime, t_timestamp, t_year) values ('10:57:50', '2018-7-29', '2008-8-8 20:00:00', '2018-7-29 11:00:12', 2020);
mysql> select * from timeTest;
+----------+------------+---------------------+---------------------+--------+
| t_time   | t_date     | t_datetime          | t_timestamp         | t_year |
+----------+------------+---------------------+---------------------+--------+
| 10:57:50 | 2018-07-29 | 2008-08-08 20:00:00 | 2018-07-29 11:00:12 |   2020 |
+----------+------------+---------------------+---------------------+--------+

mysql> insert into timeTest (t_time, t_date, t_datetime, t_year) values ('20:00:08', '2020-8-8', '2020-8-8 20:00:30', 2020);
mysql> select * from timeTest;
+----------+------------+---------------------+---------------------+--------+
| t_time   | t_date     | t_datetime          | t_timestamp         | t_year |
+----------+------------+---------------------+---------------------+--------+
| 20:00:08 | 2020-08-08 | 2020-08-08 20:00:30 | 2020-07-06 13:42:56 |   2020 |
+----------+------------+---------------------+---------------------+--------+

mysql> insert into timeTest (t_time, t_date, t_datetime, t_year) values ('20:00:08', '2020-8-8', now(), 2020);
mysql> select * from timeTest;
+----------+------------+---------------------+---------------------+--------+
| t_time   | t_date     | t_datetime          | t_timestamp         | t_year |
+----------+------------+---------------------+---------------------+--------+
| 20:00:08 | 2020-08-08 | 2020-07-06 13:45:04 | 2020-07-06 13:45:04 |   2020 |
+----------+------------+---------------------+---------------------+--------+

mysql> update timeTest set t_year=2021 where t_year=2020;
mysql> select * from timeTest;
+----------+------------+---------------------+---------------------+--------+
| t_time   | t_date     | t_datetime          | t_timestamp         | t_year |
+----------+------------+---------------------+---------------------+--------+
| 20:00:08 | 2020-08-08 | 2020-08-08 20:00:30 | 2020-07-06 13:54:25 |   2021 |
| 20:00:08 | 2020-08-08 | 2020-07-06 13:45:04 | 2020-07-06 13:54:25 |   2021 |
+----------+------------+---------------------+---------------------+--------+