YEAR
类型通常用来表示年DATE
类型通常用来表示年、月、日TIME
类型通常用来表示时、分、秒DATETIME
类型通常用来表示年、月、日、时、分、秒TIMESTAMP
类型通常用来表示带时区的年、月、日、时、分、秒类型 | 名称 | 字节 | 日期格式 | 最小值 | 最大值 |
---|---|---|---|---|---|
YEAR | 年 | 1 | YYYY或YY | 1901 | 2155 |
TIME | 时间 | 3 | HH:MM:SS | -838:59:59 | 838:59:59 |
DATE | 日期 | 3 | YYYY-MM-DD | 1000-01-01 | 9999-12-03 |
DATETIME | 日期时间 | 8 | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
TIMESTAMP | 日期时间 | 4 | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:00 UTC | 2038-01-19 03:14:07UTC |
TIME
的取值范围不是 -23:59:59~23:59:59
呢?YEAR
类型用来表示年份,在所有的日期时间类型中所占用的存储空间最小,只需要1个字节
的存储空间。
在MySQL中,YEAR有以下几种存储格式:
YYYY
”,没必要写成YEAR(4)
,从MySQL 8.0.19开始,不推荐使用指定显示宽度的YEAR(4)
数据类型。CREATE TABLE test_year(
f1 YEAR,
f2 YEAR(4)
);
mysql> DESC test_year;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| f1 | year(4) | YES | | NULL | |
| f2 | year(4) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
INSERT INTO test_year
VALUES('45','71');
INSERT INTO test_year
VALUES(0,'0');
mysql> SELECT * FROM test_year;
+------+------+
| f1 | f2 |
+------+------+
| 2020 | 2021 |
| 2045 | 1971 |
| 0000 | 2000 |
+------+------+
3 rows in set (0.00 sec)
DATE类型表示日期,没有时间部分,格式为YYYY-MM-DD
,其中,YYYY
表示年份,MM
表示月份,DD
表示日期。需要3个字节
的存储空间。在向DATE类型的字段插入数据时,同样需要满足一定的格式条件。
YYYY-MM-DD
格式或者YYYYMMDD
格式表示的字符串日期,其最小取值为1000-01-01
,最大取值为9999-12-03
。YYYYMMDD
格式会被转化为YYYY-MM-DD
格式。YY-MM-DD
格式或者YYMMDD
格式表示的字符串日期,此格式中,年份为两位数值或字符串满足YEAR类型的格式条件为:当年份取值为00到69时,会被转化为2000到2069;当年份取值为70到99时,会被转化为1970到1999。CURRENT_DATE()
或者NOW()
函数,会插入当前系统的日期。举例:
创建数据表,表中只包含一个DATE类型的字段f1。
CREATE TABLE test_date1(
f1 DATE
);
Query OK, 0 rows affected (0.13 sec)
插入数据:
建议使用第一种写法
INSERT INTO test_date1
VALUES ('2020-10-01'), ('20201001'),(20201001);
INSERT INTO test_date1
VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), ('700101'), ('99-01-01'), ('990101');
INSERT INTO test_date1
VALUES (000301), (690301), (700301), (990301); # 存在隐式转换
TIME类型用来表示时间,不包含日期部分。在MySQL中,需要3个字节
的存储空间来存储TIME类型的数据,可以使用“HH:MM:SS
”格式来表示TIME
类型,其中,HH表示小时,MM表示分钟,SS表示秒。
在MySQL中,向TIME类型的字段插入数据时,也可以使用几种不同的格式。
(1)可以使用带有冒号的字符串,比如'D HH:MM:SS'
、'HH:MM:SS
'、'HH:MM
'、'D HH:MM
'、'D HH
'或'SS
'格式,都能被正确地插入TIME类型的字段中。其中D表示天,其最小值为0,最大值为34。如果使用带有D格式的字符串插入TIME类型的字段时,D会被转化为小时,计算格式为D*24+HH。当使用带有冒号并且不带D的字符串表示时间时,表示当天的时间,比如12:10表示12:10:00,而不是00:12:10。
(2)可以使用不带有冒号的字符串或者数字,格式为'HHMMSS
'或者HHMMSS
。如果插入一个不合法的字符串或者数字,MySQL在存储数据时,会将其自动转化为00:00:00进行存储。比如1210,MySQL会将最右边的两位解析成秒,表示00:12:10,而不是12:10:00。
(3)使用CURRENT_TIME()
或者NOW()
,会插入当前系统的时间。
举例:
创建数据表,表中包含一个TIME类型的字段f1。
CREATE TABLE test_time1(
f1 TIME
);
Query OK, 0 rows affected (0.02 sec)
INSERT INTO test_time1
VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');
INSERT INTO test_time1
VALUES ('123520'), (124011),(1210);
INSERT INTO test_time1
VALUES (NOW()), (CURRENT_TIME());
SELECT * FROM test_time1;
DATETIME类型在所有的日期时间类型中占用的存储空间最大,总共需要8
个字节的存储空间。在格式上为DATE类型和TIME类型的组合,可以表示为YYYY-MM-DD HH:MM:SS
,其中YYYY表示年份,MM表示月份,DD表示日期,HH表示小时,MM表示分钟,SS表示秒。
在向DATETIME类型的字段插入数据时,同样需要满足一定的格式条件。
YYYY-MM-DD HH:MM:SS
格式或者YYYYMMDDHHMMSS
格式的字符串插入DATETIME类型的字段时,最小值为1000-01-01 00:00:00,最大值为9999-12-03 23:59:59。YY-MM-DD HH:MM:SS
格式或者YYMMDDHHMMSS
格式的字符串插入DATETIME类型的字段时,两位数的年份规则符合YEAR类型的规则,00到69表示2000到2069;70到99表示1970到1999。CURRENT_TIMESTAMP()
和NOW()
,可以向DATETIME类型的字段插入系统的当前日期和时间。举例:
创建数据表,表中包含一个DATETIME类型的字段dt。
CREATE TABLE test_datetime1(
dt DATETIME
);
Query OK, 0 rows affected (0.02 sec)
插入数据:
INSERT INTO test_datetime1
VALUES ('2021-01-01 06:50:30'), ('20210101065030');
INSERT INTO test_datetime1
VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'), ('200101000000');
INSERT INTO test_datetime1
VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);
INSERT INTO test_datetime1
VALUES (CURRENT_TIMESTAMP()), (NOW());
TIMESTAMP类型也可以表示日期时间,其显示格式与DATETIME类型相同,都是YYYY-MM-DD HH:MM:SS
,需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多,只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中,UTC表示世界统一时间,也叫作世界标准时间。
向TIMESTAMP类型的字段插入数据时,当插入的数据格式满足YY-MM-DD HH:MM:SS和YYMMDDHHMMSS时,两位数值的年份同样符合YEAR类型的规则条件,只不过表示的时间范围要小很多。
如果向TIMESTAMP类型的字段插入的时间超出了TIMESTAMP类型的范围,则MySQL会抛出错误信息。
举例:
创建数据表,表中包含一个TIMESTAMP类型的字段ts。
CREATE TABLE test_timestamp1(
ts TIMESTAMP
);
插入数据:
INSERT INTO test_timestamp1
VALUES ('1999-01-01 03:04:50'), ('19990101030405'), ('99-01-01 03:04:05'), ('990101030405');
INSERT INTO test_timestamp1
VALUES ('2020@01@01@00@00@00'), ('20@01@01@00@00@00');
INSERT INTO test_timestamp1
VALUES (CURRENT_TIMESTAMP()), (NOW());
#Incorrect datetime value,超出范围了
INSERT INTO test_timestamp1
VALUES ('2038-01-20 03:14:07');
TIMESTAMP和DATETIME的区别:
CREATE TABLE temp_time(
d1 DATETIME,
d2 TIMESTAMP
);
INSERT INTO temp_time VALUES('2021-9-2 14:45:52','2021-9-2 14:45:52');
INSERT INTO temp_time VALUES(NOW(),NOW());
mysql> SELECT * FROM temp_time;
+---------------------+---------------------+
| d1 | d2 |
+---------------------+---------------------+
| 2021-09-02 14:45:52 | 2021-09-02 14:45:52 |
| 2021-11-03 17:38:17 | 2021-11-03 17:38:17 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
#修改当前的时区
SET time_zone = '+9:00';
mysql> SELECT * FROM temp_time;
+---------------------+---------------------+
| d1 | d2 |
+---------------------+---------------------+
| 2021-09-02 14:45:52 | 2021-09-02 15:45:52 |
| 2021-11-03 17:38:17 | 2021-11-03 18:38:17 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
DATETIME
。虽然 MySQL 也支持 YEAR(年)、 TIME(时间)、DATE(日期),以及 TIMESTAMP 类型,但是在实际项目中,尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息,取值范围也最大,使用起来比较方便。毕竟,如果日期时间信息分散在好几个字段,很不容易记,而且查询的时候,SQL 语句也会更加复杂。 时间戳
,因为DATETIME虽然直观,但不便于计算。mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| 1645624817 |
+------------------+
1 row in set (0.00 sec)
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。