MySQL数据类型之时间和日期型

非计算机专业数据库系统原理 2019-10-08

1705

根据MySQL官方文档，MySQL5.7版本和MySQL8.0版本的数据类型主要分为5种^[1,2]。前三种为常用的数据类型。

1.数值型 Numeric Types

2.日期与时间型 Date and Time Types

3.字符串型 String Types

4.空间数据类型Spatial Data Types

5.JSON数据类型

本文介绍的是日期与时间类型Date and Time Types，包括三类：

1. DATE, DATETIME, and TIMESTAMP

2. TIME

3. YEAR

1. DATE, DATETIME, and TIMESTAMP

DATE：

DATE日期类型用于具有日期部分但没有时间部分的值。

MySQL以“yyyy-mm-dd”格式检索并显示日期值。

支持的范围是“1000-01-01”到“9999-12-31”。

DATETIME：

DATETIME日期时间类型用于包含日期和时间部分的值。

MySQL以“yyyy-mm-dd hh:mm:ss”格式检索并显示日期时间值。

支持的范围是“1000-01-01 00:00:00”到“9999-12-31 23:59:59”。

TIMESTAMP：

TIMESTAMP时间戳数据类型用于包含日期和时间部分的值。

TIMESTAMP时间戳的范围为UTC“1970-01-01 00:00:01”到UTC“2038-01-19 03:14:07”。

MySQL将TIMESTAMP时间戳值从当前时区转换为UTC(世界协调时)进行存储，并从UTC(世界协调时)转换回当前时区进行检索。

时间戳和日期时间数据类型提供对当前日期和时间的自动初始化和更新。^[3]

2. TIME

MySQL以“hh:mm:ss”格式检索并显示TIME时间值(对于大小时值(large hours values)，则为“hhh:mm:ss”格式)。

时间值的范围可以从'-838:59:59 '到' 838:59:59 '。

小时部分可能很大，因为时间类型不仅可以用来表示一天中的时间(必须少于24小时)，还可以表示经过的时间或两个事件之间的时间间隔(可能远远大于24小时，甚至是负数)。

3. YEAR

YEAR数据类型用于表示年份，存储长度为1字节。

它可以声明为YEAR或YEAR(4),显示宽度为四个字符。

MySQL以YYYY格式显示年份值，范围为1901到2155或0000。

可以用多种格式输入YEAR值：

1901到2155之间的4位数字。
作为“1901”到“2155”范围内的4位数字符串
1到99之间的1或2位数。MySQL将1到69和70到99范围内的值转换为2001到2069和1970到1999范围内的YEAR值。
作为“0”到“99”范围内的1或2位数字符串。MySQL将“0”到“69”和“70”到“99”范围内的值转换为2000到2069和1970到1999范围内的YEAR值。

注：MySQL 8.0不支持MySQL旧版本中允许的YEAR(2)数据类型。

时间类型转换的函数

MySQL为不同时间类型的转换、String类型和时间类型的转换等设定了不同的函数[4]，其中的STR_TO_DATE(str,format)函数可以将字符串类型转为合法的MySQL时间类型。

字符串转时间：STR_TO_DATE(str,format)

与DATE_FORMAT ()函数相反。STR_TO_DATE(str,format)采用str字符串和format格式字符串格式。

如果格式字符串同时包含日期和时间部分，则STR_TO_DATE(str,format)返回日期时间值；
如果字符串仅包含日期或时间部分，则返回日期或时间值。
如果从str中提取的日期、时间或日期时间值非法，STR_TO_DATE ()返回null并产生警告。

【例】

将字符串'01,5,2013'转换为合法的MySQL时间类型。

mysql>SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');        
   -> '2013-05-01'
复制

将字符串'May 1, 2013'转换为合法的MySQL时间类型。

mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');        
   -> '2013-05-01'
复制

%d：Day of the month, numeric (00..31)

%M：Month name (January..December)

%m：Month, numeric (00..12)

%Y：Year, numeric, four digits

STR_TO_DATE(str,format)的完整说明可参照：

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date

参考文献：

[1] DataTypes.MySQL 8.0 Reference Manual. https://dev.mysql.com/doc/refman/8.0/en/char.html

[2] DataTypes.MySQL 5.7 Reference Manual. https://dev.mysql.com/doc/refman/5.7/en/char.html

[3] Automatic Initialization and Updating for TIMESTAMP and DATETIME.MySQL 8.0 Reference Manual.https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

[4] Date and Time Functions. MySQL 8.0 Reference Manual.https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

mysql

文章转载自非计算机专业数据库系统原理，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL数据类型之时间和日期型

评论

相关阅读