MySQL中datetime、date、time、str之间如何转化

共计 6331 个字符，预计需要花费 16 分钟才能阅读完成。

这篇文章主要介绍“MySQL 中 datetime、date、time、str 之间如何转化”的相关知识，丸趣 TV 小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“MySQL 中 datetime、date、time、str 之间如何转化”文章能帮助大家解决问题。

datetime、date、time、str 之间的转化与比较

SELECT NOW(),CURDATE(),CURTIME(),
 -- datetime  转  date time
 NOW(), DATE(NOW()), TIME (NOW()), CONCAT(DATE(NOW()),    , TIME (NOW())),
 -- str  转  datetime date time
 str_to_date(2019-04-25 08:50:00 ,  %Y-%m-%d %H:%i:%s),DATE(2019-04-25 08:50:00),TIME (2019-04-25 08:50:00),
 str_to_date(2019-04-25 08:50:00 ,  %Y-%m-%d %T),
 --  比较
 NOW() = CONCAT(DATE(NOW()),    , TIME (NOW())),
 DATE(NOW()) =  2019-04-25 ,
 NOW() = DATE(NOW()),
 NOW() = TIME (NOW());
 
--  获取当前时间的函数
select CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME; --  当前时间
select CURRENT_TIMESTAMP(), CURRENT_DATE(), CURRENT_TIME(), CURDATE(), CURTIME(); --  当前时间
select now(), sysdate(), SLEEP(3), now(), sysdate(); --  当前时间
select UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME(); -- UTC 时间
select UNIX_TIMESTAMP(), UNIX_TIMESTAMP(now());--  函数返回连接当前时区内的值（时间戳）select FROM_UNIXTIME(UNIX_TIMESTAMP(), %Y-%m-%d %H:%i:%s  --  时间戳格式化
select DATE_FORMAT(now(), %Y-%m-%d %H:%i:%s  --  时间格式化
-- -----------------  补充日期操作
/*  对日期进行   加减（两个函数记一个即可）DATE_ADD(date,INTERVAL expr type);
DATE_SUB(date,INTERVAL expr type);
type 取值：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、YEAR_MONTH、DAY_SECOND  等等
--  加 1 天
select DATE_ADD(now(),INTERVAL 1 DAY), DATE_SUB(now(),INTERVAL -1 DAY);
--  复合型：加 1 年 1 月
select DATE_ADD(now(),INTERVAL  1 1  YEAR_MONTH), DATE_SUB(now(),INTERVAL  -1 -1  YEAR_MONTH);
--  加 1 天 2 时 3 分 4 秒
select DATE_ADD(2020-12-23 00:00:00 ,INTERVAL  1 2:3:4  DAY_SECOND),DATE_SUB(2020-12-23 00:00:00 ,INTERVAL  -1 -2:-3:-4  DAY_SECOND);
/*  获取两个日期之间的差值（datetime_expr2-datetime_expr1 的值）TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
unit：差值的单位，取值：FRAC_SECOND（低版本不支持）、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER 或 YEAR
--  查询两个日期间的差值（单位秒）select TIMESTAMPDIFF(SECOND, 2020-12-01 00:00:00 , 2020-12-01 01:01:01

MySQL 日期和时间数据类型(DATE、TIME、DATETIME、TIMESTAMP 和 YEAR

本文基于 MySQL 5.7 版本。

MySQL 中表示时间值的日期和时间数据类型有以下五个：

（1）DATE

（2）TIME

（3）DATETIME

（4）TIMESTAMP

（5）YEAR

日期和时间数据类型语法

MySQL 允许对 TIME、DATETIME 和 TIMESTAMP 使用小数秒，精度最高可达微秒（6 位小数）。要定义包含小数秒日期和时间数据类型的列，使用

type_name(fsp)，

其中 type_name 是 TIME、DATETIME 或 TIMESTAMP，而 fsp 是小数秒精度，fsp 的值，如果给定，必须在 0 ~ 6 之间。值为 0 表示没有小数部分。如果省略，默认精度为 0。

例如:

CREATE TABLE date_time_test (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));

Date

支持的范围 1000-01-01 to 9999-12-31，MySQL 以 YYYY-MM-DD 格式显示 Date 类型，但允许使用字符串或数字将值赋给 DATE 列。字面常量转换规则如下：

（1）格式为 YYYY-MM-DD 或 YY-MM-DD 的字符串。任何标点字符都可以用作日期部分之间的分隔符。例如，2012-12-31、2012/12/31、2012^12^31 和 2012@12@31 是等价的。

（2）格式为 YYYYMMDD 或 YYMMDD 的没有分隔符的字符串，只要该字符串具有日期的意义。例如，20070523 和 070523 被解释为 2007-05-23，但 071332 是非法的(它有无意义的月和日部分)，会变成 0000-00-00。

（3）作为 YYYYMMDD 或 YYMMDD 格式的数字，只要该数字作为日期有意义。例如，19830905 和 830905 被解释为“1983-09-05”。

DATETIME 和 DATETIME(fsp)

DATETIME 类型是 Date 和 Time 的组合。支持的范围从 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999。MySQL 以 YYYY-MM-DD hh:mm:ss[.fraction] 的格式显示 DATETIME 类型。但允许使用字符串或数字向 DATETIME 列赋值。字面常量转换规则如下：

表中的任何 TIMESTAMP 或 DATETIME 列都可以具有自动初始化和更新属性。

（1）格式为“YYYY-MM-DD hh:mm:ss”或“YY-MM-DD hh:mm:ss”字符串。任何标点字符都可以用作日期部分或时间部分之间的分隔符。例如，2012-12-31 11:30:45、2012^12^31 11+30+45、2012/12/31 11*30*45 和 2012@12@31 11^30^45 是等价的。

（2）在日期和时间部分和小数秒部分之间识别的唯一分隔符是小数点。

（3）日期和时间部分可以用 T 而不是空格分隔。例如，2012-12-31 11:30:45 2012-12-31 t11:30:45 是等价的。

（4）格式为 YYYYMMDDhhmmss 或 YYMMDDhhmmss 无分隔符的字符串，只要该字符串具有日期的意义。例如，20070523091528 和 070523091528 被解释为 2007-05-23 09:15:28，但 071122129015 是非法的(它有一个无意义的分钟部分)，会变成 0000-00-00 00:00:00。

（5）YYYYMMDDhhmmss 或 YYMMDDhhmmss 格式的数字，只要该数字作为日期有意义。例如，19830905132800 和 830905132800 被解释为“1983-09-05 13:28:00”。

TIMESTAMP 和 TIMESTAMP[(fsp)]

支持的范围 1970-01-01 00:00:01.000000 UTC to 2038-01-19 03:14:07.999999 UTC（UTC 指的是世界标准时间），TIMESTAMP 值存储为自纪元 (1970-01-01 00:00:00 UTC) 以来的秒数，但是它不能表示 1970-01-01 00:00:00，因为这表示从纪元开始的 0 秒，值 0 保留用于表示“0000-00-00 00:00:00”，即“零”的 TIMESTAMP 值。TIMESTAMP 的字面常量同 DATETIME。

上面 date_time_test 表 ts 列是 TIMESTAMP，一旦创建后，我们发现 ts 列默认值为当前 TIMESTAMP，而且可自动更新为当前时间戳，如图：

支持的范围 1970-01-01 00:00:01.000000 UTC to 2038-01-19 03:14:07.999999 UTC（UTC 指的是世界标准时间），TIMESTAMP 值存储为自纪元 (1970-01-01 00:00:00 UTC) 以来的秒数，但是它不能表示 1970-01-01 00:00:00，因为这表示从纪元开始的 0 秒，值 0 保留用于表示“0000-00-00 00:00:00”，即“零”的 TIMESTAMP 值。TIMESTAMP 的字面常量同 DATETIME。

上面 date_time_test 表 ts 列是 TIMESTAMP，一旦创建后，我们发现 ts 列默认值为当前 TIMESTAMP，而且可自动更新为当前时间戳，如图：

向 date_time_test 表中插入一条数据，如下：

insert date_time_test(t) values(00:00:01

登录后复制

查看数据，如图：

发现 ts 列自动更新为插入数据时的时间戳。

更新当前数据，如图：

update date_time_test set t= 00:00:02  where t= 00:00:01

登录后复制

再次查看时间戳，如图：

ts 列已经更新为当前时间戳。这和 MySQL 的系统变量 explicit_defaults_for_timestamp 有关，

show variables like  explicit_defaults_for_timestamp

登录后复制

如图：

explicit_defaults_for_timestamp 处于关闭状态，此时，插入数据时，如果 ts 没有指定值，会被初始化为默认值，更新数据时，ts 列同时更新。explicit_defaults_for_timestamp 处于打开状态，不会被初始化为默认，也不具备自动更新为当前时间戳。也可以对 TIMESTAMP 和 DATETIME 如下显示定义默认值和自动更新：

CREATE TABLE t1 (
 ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

TIME 和 TIME(fsp)

支持的范围 -838:59:59.000000 to 838:59:59.000000，MySQL 以 hh:mm:ss[.fraction] 格式显示 TIME，但允许使用字符串或数字向 TIME 列赋值。TIME 类型这个时间范围表明它不仅可以表示一天的时间，也可以表示经过的时间或两个事件之间的时间间隔。字面常量转换规则如下：

（1）MySQL 将带有冒号的 TIME 值缩写为一天中的时间，例如：11:12 表示 11:12:00，而不是 00:11:12；

（2）MySQL 将没有冒号的 TIME 缩写值最右边的两个数字看成秒，例如：1112 和 1112 都表示 00:11:12

（3）在时间部分和小数秒部分之间识别的唯一分隔符是小数点；

（4）默认情况下，位于 TIME 范围之外但在其他方面有效的值将被裁剪到该范围中最近的端点。例如，-850:00:00 和 850:00:00 会转换为 -838:59:59 和 838:59:59。无效的 TIME 值将被转换为“00:00:00”。注意，由于 00:00:00 本身是一个有效的 TIME 值，因此无法从存储在表中的 00:00:00 值判断原始值是 00:00:00 还是无效的。

YEAR 和 YEAR(4)

支持的范围 1901 到 2155，或者 0000。MySQL 以 YYYY 格式显示 YEAR。但允许使用字符串或数字向 YEAR 列赋值。

SUM()和 AVG()聚合函数不能处理时间值。要解决这个问题，请将其转换为数字，然后执行聚合操作，最后将其转换回时间值。例子:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;

YEAR 接受如下格式的输入：

（1）4 位字符串 1901 到 2155；

（2）4 位数字 1901 到 2155；

（3）1 或者 2 位字符串 0 到 99，MySQL 将 0 到 69 转化为 2000 到 2069，将 70 到 99 转化为 1970 到 1999；

（4）1 或者 2 位数字 0 到 99，MySQL 将 1 到 69 转换为 YEAR 值 2001 到 2069，将 70 到 99 转化为 YEAR 值 1970 到 1999，0 转化为 YEAR 值 0000。

注：

如果没有启用 strict SQL 模式，MySQL 会将无效的 YEAR 值转换为 0000。在严格 SQL 模式下，试图插入无效的 YEAR 值将产生错误。

总结如下：

DATETIMEDATETIMETIMESTAMPYEAR 取值范围

1000-01-01

到

9999-12-31

-838:59:59.000000

到

838:59:59.000000

1000-01-01 00:00:00.000000 到

9999-12-31 23:59:59.999999

1970-01-01 00:00:01.000000 UTC to

2038-01-19 03:14:07.999999 UTC

1901

到

2155

或者 0000

小数位精度 00~60~60~60 显示格式 YYYY-MM-DDhh:mm:ss[.fraction]YYYY-MM-DD hh:mm:ss[.fraction]YYYY-MM-DD hh:mm:ss[.fraction]YYYY 赋值允许使用符合格式的字符串或数字赋值允许使用符合格式的字符串或数字赋值允许使用符合格式的字符串或数字赋值允许使用符合格式的字符串或数字赋值允许使用符合格式的字符串或数字赋值小数位分隔符无只识别小数点. 只识别小数点. 只识别小数点. 无日期分隔符任何标点无任何标点任何标点无时间分隔符无任何标点任何标点任何标点无无效值处理（strict mode 关闭状态）

转化为

0000-00-00

转化为

0000-00-00 00:00:00

转化为

0000-00-00 00:00:00

转换为

00:00:00

转换为 0000

关于“MySQL 中 datetime、date、time、str 之间如何转化”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识，可以关注丸趣 TV 行业资讯频道，丸趣 TV 小编每天都会为大家更新不同的知识点。