MySQL中共有5种时间日期类型
类型名称 | 时间格式 | 取值范围 | MySQL5.6.4之前存储需求(字节) | MySQL5.6.4开始存储需求 (字节) |
year | YYYY | 1901 ~ 2155 | 1 | 1 |
date | YYYY-MM-DD | 1000-01-01 ~ 9999-12-3 | 3 | 3 |
time | hh:mm:ss | -838:59:59 .000000~ 838:59:59.000000 | 3 | 3+小数秒存储 |
timestamp | YYYY-MM-DD hh:mm:ss | 1980-01-01 00:00:01.000000 UTC ~ 2040-01-19 03:14:07.999999UTC | 4 | 4+小数秒存储 |
datetime | YYYY-MM-DD hh:mm:ss | 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999 | 8 | 5+小数秒存储 |
在MySQL5.6.4开始允许time、timestamp、datetime具有小数部分,会需要0~3个字节
YEAR
YEAR存储所需1个字节,在MySQL8.0以后,创建该数据类型时,只能声明为YEAR或者YEER(4),不支持YEAR(2)
在MyDQL中以YYYY格式显示,值的范围为1901~2155和0000
- 输入时,允许使用数字和字符串,如2019、 '2019'
- 当输入的是4位数字或字符串时,必须保证其值在取值范围内,即1901~2155、'1901'~'2155',否则MySQL在存储时会报错
- 当输入的是1到两位数字或字符串,即1~99、'1'~'99'时,MySQL会将1~69、'1'~'69'转化为2001~2069;70~99、'70'~'99'转化为1970~1999
- 当输入的是0,00,000,0000,00000等时,MySQl会将其转化为0000
- 当输入的是'0','00','000','0000','00000'等时,MySQL会将其转化为2000
- 当输入的是09,029,02019等时,与输入9,29,2019效果相同,只识别有效数字是否符合要求
- 当输入的是三位有效数字或字符串,如234,122与'234'、'122'等时,MySQL在存储时会报错
DATE
DATE需要3个字节其显示格式为YYYY-MM-DD,其取值范围为1000-01-01 ~ 9999-12-3
- 只具有日期部分部分,不具有时间部分
- 允许没有分隔符输入数字和字符串,但其代表月份日期为单数时必须使用0占位,如7月3号,输入时应为0703
- 当输入的有效数字和字符串为20190727,'20190727'MySQL会将其转化为2019-07-27
- 当输入的有效数字为190727,90727,'190727','90727',MySQL会将其转化为2019-07-27
- MySQL允许在输入字符串使用“不严格”模式,任何标点字符都都可以作为日期和时间的分隔符,如'2019:7:27','2019,7,27‘’,'19-7-27'在date类型中,MySQL会自动转化为2019-07-27
- 其中代表年份的部分与YEAR中一样,1~99、'1'~'99'时,MySQL会将1~69、'1'~'69'转化为2001~2069;70~99、'70'~'99'转化为1970~1999
DATETIME、TIMESTAMP
相同点:
- 两者都同时具有日期和时间
- 显示格式相同,YYYY-MM-DD hh:mm:ss
- 都可以自动初始化和更新当前时间和日期(当前时间戳)
不同点
- 取值范围不同。DATETIME的取值范围为1000-01-01 00:00:00 ~9999-12-31 23:59:59;TIMESTAMP的取值范围为1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC。
- 需要的存储的空间不同。MySQL5.6.4开始,DATETIME的存储需求为4字节+小数秒存储空间;TIMESTAMP的存储需求为5字节+小数秒存储空间
- TIMESTAMP会将时间转化为UTC(世界标准时间)进行存储,在查询时会将其转化为当前时区进行返回。而DATETIME则不可以
输入数据时
- 允许没有分隔符输入数字和字符串,但其代表月份、日期、时、分、秒为单数时必须使用0占位,如7月3号9时43分5秒,输入时应为0703094305
- 当输入的有效数字和字符串为20190727090909,'20190727090909'MySQL会将其转化为2019-07-27 09:09:09
- MySQL允许在输入字符串使用“不严格”模式,任何标点字符都都可以作为日期和时间的分隔符,如'2019:7:27 09:09:09','2019,7,27 9,9,9‘’,'19-7-27 9-9-9'在date类型中,MySQL会自动转化为2019-07-27 09:09:09。这里的分隔符可以随意组合,但有一点需要注意,日期和时间之间没有分隔符,用空格分开
- 其中代表年份的部分与YEAR中一样,1~99、'1'~'99'时,MySQL会将1~69、'1'~'69'转化为2001~2069;70~99、'70'~'99'转化为1970~1999
TIME
MySQL5.6.4开始,TIME的存储需求为3个字节+小数秒存储空间,其显示格式为hh:mm:ss,取值范围为-838:59:59 ~ 838:59:59
- TIME可以表示一天的时间,此时其值必须是24小时制
- TIME也可以表示两个事件之间的经过时间或时间间隔,此时可以为负数,小时可以大于24小时
- 也允许输入没有分隔符的数字和字符串,其有效数字必须在六位即六位以下,MySQL会一次从右向左进行转换,剩下的将自动补充为0,即输入'123'、987654,MySQL将自动转为00:01:23、'98:76:54'
- 在TIME中时间的分隔符必须为冒号,但有一点需要注意,如果输入的值为'11:22'时,MySQL会将其转化为11:22:00,而非00:11:22
- 小数秒部分的分隔符必须为小数点
TIME、DATETIME、TIMESTAMP其小数秒部分唯一的分隔符时小数点,没有其他分隔符。虽然这三者没有显示小数秒部分,但不代表小数秒部分被丢弃,其值中的小数秒部分都会被保存
最后,详细解释请见MySQL官网https://dev.mysql.com/doc/refman