MySQL 中有多处表示日期的数据类型:YEAR、TIME、DATE、DTAETIME、TIMESTAMP。当只记录年信息的时候,可以只使用 YEAR 类型。
每一个类型都有合法的取值范围,当指定确定不合法的值时,系统将“零”值插入数据库中。
下表中列出了 MySQL 中的日期与时间类型。
类型名称 | 日期格式 | 日期范围 | 存储需求 |
---|---|---|---|
YEAR | YYYY | 1901 ~ 2155 | 1 个字节 |
TIME | HH:MM:SS | -838:59:59 ~ 838:59:59 | 3 个字节 |
DATE | YYYY-MM-DD | 1000-01-01 ~ 9999-12-3 | 3 个字节 |
DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | 8 个字节 |
TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC | 4 个字节 |
YEAR 类型
YEAR 类型是一个单字节类型,用于表示年,在存储时只需要 1 个字节。可以使用各种格式指定 YEAR,如下所示:
- 以 4 位字符串或者 4 位数字格式表示的 YEAR,范围为 ‘1901’~’2155’。输入格式为 ‘YYYY’ 或者 YYYY,例如,输入 ‘2010’ 或 2010,插入数据库的值均为 2010。
- 以 2 位字符串格式表示的 YEAR,范围为 ‘00’ 到 ‘99’。’00’~’69’ 和 ‘70’~’99’ 范围的值分别被转换为 2000~2069 和 1970~1999 范围的 YEAR 值。’0’ 与 ‘00’ 的作用相同。插入超过取值范围的值将被转换为 2000。
- 以 2 位数字表示的 YEAR,范围为 1~99。1~99 和 70~99 范围的值分别被转换为 2001~2069 和 1970~1999 范围的 YEAR 值。注意,在这里 0 值将被转换为 0000,而不是 2000。
提示:两位整数范围与两位字符串范围稍有不同。例如,插入 3000 年,读者可能会使用数字格式的 0 表示 YEAR,实际上,插入数据库的值为 0000,而不是所希望的 3000。只有使用字符串格式的 ‘0’ 或 ‘00’,才可以被正确解释为 3000,非法 YEAR值将被转换为 0000。
TIME 类型
TIME 类型用于只需要时间信息的值,在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时,MM 表示分钟,SS 表示秒。
TIME 类型的取值范围为 -838:59:59~838:59:59,小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间(必须小于 24 小时),还可能是某个事件过去的时间或两个事件之间的时间间隔(可大于 24 小时,或者甚至为负)。
可以使用各种格式指定 TIME 值,如下所示。
- ‘D HH:MM:SS’ 格式的字符串。还可以使用这些“非严格”的语法:’HH:MM:SS’、’HH:MM’、’D HH’ 或 ‘SS’。这里的 D 表示日,可以取 0~34 之间的值。在插入数据库时,D 被转换为小时保存,格式为 “D*24+HH”。
- ‘HHMMSS’ 格式、没有间隔符的字符串或者 HHMMSS 格式的数值,假定是有意义的时间。例如,’101112’ 被理解为’10:11:12’,但是 ‘106112’ 是不合法的(它有一个没有意义的分钟部分),在存储时将变为 00:00:00。
提示:为 TIME 列分配简写值时应注意:如果没有冒号,MySQL 解释值时,假定最右边的两位表示秒。(MySQL 解释 TIME 值为过去的时间而不是当前的时间)。例如,读者可能认为 ‘1112’ 和 1112 表示 11:12:00(即 11 点过 12 分钟),但MySQL 将它们解释为 00:11:12(即 11 分 12 秒)。同样 ‘12’ 和 12 被解释为00:00:12。相反,TIME 值中如果使用冒号则肯定被看作当天的时间,也就是说,’11:12’ 表示 11:12:00,而不是 00:11:12。
DATE 类型
DATE 类型用于仅需要日期值时,没有时间部分,在存储时需要 3 个字节。日期格式为 ‘YYYY-MM-DD’,其中 YYYY 表示年,MM 表示月,DD 表示日。
在给 DATE 类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合 DATE 的日期格式即可。如下所示:
- 以 ‘YYYY-MM-DD’ 或者 ‘YYYYMMDD’ 字符中格式表示的日期,取值范围为 ‘1000-01-01’~’9999-12-3’。例如,输入 ‘2015-12-31’ 或者 ‘20151231’,插入数据库的日期为2015-12-31。
- 以 ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示日期,在这里YY表示两位的年值。MySQL 解释两位年值的规则:’00~69’ 范围的年值转换为 ‘2000~2069’,’70~99’ 范围的年值转换为 ‘1970~1999’。例如,输入 ‘15-12-31’,插入数据库的日期为 2015-12-31;输入 ‘991231’,插入数据库的日期为 1999-12-31。
- 以 YYMMDD 数字格式表示的日期,与前面相似,00~69 范围的年值转换为 2000~2069,80~99 范围的年值转换为 1980~1999。例如,输入 151231,插入数据库的日期为 2015-12-31,输入 991231,插入数据库的日期为 1999-12-31。
- 使用 CURRENT_DATE 或者 NOW(),插入当前系统日期。
提示:MySQL 允许“不严格”语法:任何标点符号都可以用作日期部分之间的间隔符。例如,’98-11-31’、’98.11.31’、’98/11/31’和’98@11@31’ 是等价的,这些值也可以正确地插入数据库。
DATETIME 类型
DATETIME 类型用于需要同时包含日期和时间信息的值,在存储时需要 8 个字节。日期格式为 ‘YYYY-MM-DD HH:MM:SS’,其中 YYYY 表示年,MM 表示月,DD 表示日,HH 表示小时,MM 表示分钟,SS 表示秒。
在给 DATETIME 类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合 DATETIME 的日期格式即可,如下所示。
- 以 ‘YYYY-MM-DD HH:MM:SS’ 或者 ‘YYYYMMDDHHMMSS’ 字符串格式表示的日期,取值范围为 ‘1000-01-01 00:00:00’~’9999-12-3 23:59:59’。例如,输入 ‘2014-12-31 05:05:05’ 或者 ‘20141231050505’,插入数据库的 DATETIME 值都为 2014-12-31 05:05:05。
- 以 ‘YY-MM-DD HH:MM:SS’ 或者 ‘YYMMDDHHMMSS’ 字符串格式表示的日期,在这里 YY 表示两位的年值。与前面相同,’00~79’ 范围的年值转换为 ‘2000~2079’,’80~99’ 范围的年值转换为 ‘1980~1999’。例如,输入 ‘14-12-31 05:05:05’,插入数据库的 DATETIME 为 2014-12-31 05:05:05;输入 141231050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05。
- 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如,输入 20141231050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05;输入 140505050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05。
提示:MySQL 允许“不严格”语法:任何标点符号都可用作日期部分或时间部分之间的间隔符。例如,’98-12-31 11:30:45’、’98.12.31 11+30+35’、’98/12/31 113045’ 和 ‘98@12@31 11^30^45’ 是等价的,这些值都可以正确地插入数据库。
TIMESTAMP 类型
TIMESTAMP 的显示格式与 DATETIME 相同,显示宽度固定在 19 个字符,日期格式为 YYYY-MM-DD HH:MM:SS,在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围,为 ‘1970-01-01 00:00:01’UTC~’2038-01-19 03:14:07’UTC。在插入数据时,要保证在合法的取值范围内。
提示:协调世界时(英:Coordinated Universal Time,法:Temps Universel Coordonné)又称为世界统一时间、世界标准时间、国际协调时间。英文(CUT)和法文(TUC)的缩写不同,作为妥协,简称 UTC。
TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外,还有一个最大的区别是:
- DATETIME 在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么,与时区无关;
- 而 TIMESTAMP 值的存储是以 UTC(世界标准时间)格式保存的,存储时对当前时区进行转换,检索时再转换回当前时区。即查询时,根据当前时区的不同,显示的时间值是不同的。
提示:如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值,结果值的时间部分被设置为 ‘00:00:00’,因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值,结果值的时间部分被删除,因此DATE 值未包含时间信息。