-
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 |
+----------+------------+---------------------+---------------------+--------+