MySQL有多种表示日期和时间的数据类型,不同的版本可能有所差异,MySQL8.0版本支持的日期和时间类型主要有:YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。
-
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 |
1、YEAR类型
YEAR类型用来表示年份,在所有的日期时间类型中所占用的存储空间最小,只需要1个字节
的存储空间。
从MySQL5.5.27开始,2位格式的YEAR已经不推荐使用。
2、DATE类型
DATE类型表示日期,没有时间部分,格式为YYYY-MM-DD
,其中,YYYY表示年份,MM表示月份,DD表示日期。需要3个字节
的存储空间。在向DATE类型的字段插入数据时,同样需要满足一定的格式条件。
使用CURRENT_DATE()
或者NOW()
函数,会插入当前系统的日期。
3、TIME类型
TIME类型用来表示时间,不包含日期部分。在MySQL中,需要3个字节
的存储空间来存储TIME类型的数据,可以使用“HH:MM:SS”格式来表示TIME类型,其中,HH表示小时,MM表示分钟,SS表示秒。
使用CURRENT_TIME()
或者NOW()
,会插入当前系统的时间。
4、DATETIME类型
DATETIME类型在所有的日期时间类型中占用的存储空间最大,总共需要8
个字节的存储空间。在格式上为DATE类型和TIME类型的组合,可以表示为YYYY-MM-DD HH:MM:SS
,其中YYYY表示年份,MM表示月份,DD表示日期,HH表示小时,MM表示分钟,SS表示秒。
使用函数CURRENT_TIMESTAMP()
和NOW()
,可以向DATETIME类型的字段插入系统的当前日期和时间。
5、TIMESTAMP类型
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存储的同一个时间值,在不同的时区查询时会显示不同的时间。
TIMESTAMP和DATETIME的区别:
- TIMESTAMP存储空间比较小,表示的日期时间范围也比较小
- 底层存储方式不同,TIMESTAMP底层存储的是毫秒值,距离1970-1-1 0:0:0 0毫秒的毫秒值。
- 两个日期比较大小或日期计算时,TIMESTAMP更方便、更快。
- TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同,显示不同的结果。而DATETIME则只能反映出插入时当地的时区,其他时区的人查看数据必然会有误差的。