日期类型
DATE TIME DATETIME TIMESTAMP YEAR
作用:存储用户注册时间,文章发布时间,员工入职时间,出生时间,过期时间等
YEAR YYYY(1901/2155) DATE YYYY-MM-DD(1000-01-01/9999-12-31) TIME HH:MM:SS('-838:59:59'/'838:59:59') DATETIME YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59 Y) TIMESTAMP YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 年某时)
#无论year指定何种宽度,最后都默认是year(4)
mysql> create table student( -> id int, -> name char(6), -> born_year year, -> birth_date date, -> class_time time, -> reg_time datetime -> ); Query OK, 0 rows affected (0.02 sec) mysql> mysql> desc student; +------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(6) | YES | | NULL | | | born_year | year(4) | YES | | NULL | | | birth_date | date | YES | | NULL | | | class_time | time | YES | | NULL | | | reg_time | datetime | YES | | NULL | | +------------+----------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
mysql 函数 now() 截取当前时间
插入一条记录 当前时间的
mysql> insert into student values(1,'mike',now(),now(),now(),now()); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from student; +------+------+-----------+------------+------------+---------------------+ | id | name | born_year | birth_date | class_time | reg_time | +------+------+-----------+------------+------------+---------------------+ | 1 | mike | 2018 | 2018-10-16 | 17:47:19 | 2018-10-16 17:47:19 | +------+------+-----------+------------+------------+---------------------+ 1 row in set (0.00 sec)
mysql> insert into student values(2,'egon',"1997","1997-12-12","12:12:12","2017-12-12 12:12:12"), -> (3,'wsb',"1998","1998-01-01","13:13:13","2017-01-01 13:13:13"); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from student; +------+------+-----------+------------+------------+---------------------+ | id | name | born_year | birth_date | class_time | reg_time | +------+------+-----------+------------+------------+---------------------+ | 1 | mike | 2018 | 2018-10-16 | 17:47:19 | 2018-10-16 17:47:19 | | 2 | egon | 1997 | 1997-12-12 | 12:12:12 | 2017-12-12 12:12:12 | | 3 | wsb | 1998 | 1998-01-01 | 13:13:13 | 2017-01-01 13:13:13 | +------+------+-----------+------------+------------+---------------------+ 3 rows in set (0.00 sec)
datetime与timestamp的区别
在实际应用的很多场景中,MySQL的这两种日期类型都能够满足我们的需要,存储精度都为秒,但在某些情况下,会展现出他们各自的优劣。
下面就来总结一下两种日期类型的区别。
1.DATETIME的日期范围是1001——9999年,TIMESTAMP的时间范围是1970——2038年。
2.DATETIME存储时间与时区无关,TIMESTAMP存储时间与时区有关,显示的值也依赖于时区。在mysql服务器,
操作系统以及客户端连接都有时区的设置。
3.DATETIME使用8字节的存储空间,TIMESTAMP的存储空间为4字节。因此,TIMESTAMP比DATETIME的空间利用率更高。
4.DATETIME的默认值为null;TIMESTAMP的字段默认不为空(not null),默认值为当前时间(CURRENT_TIMESTAMP),
如果不做特殊处理,并且update语句中没有指定该列的更新值,则默认更新为当前时间。
datetime 就能满足需求