MySQL中关于datetime、date、time、str之间的转化与比较【mysql入门】

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

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

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

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

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

DATETIME和DATETIME(fsp)

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

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

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

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

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

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

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

TIMESTAMP和TIMESTAMP[(fsp)]

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

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

支持的范围&＃39;1970-01-01 00:00:01.000000&＃39; UTC to &＃39;2038-01-19 03:14:07.999999&＃39; UTC（UTC指的是世界标准时间），TIMESTAMP值存储为自纪元(&＃39;1970-01-01 00:00:00&＃39; UTC)以来的秒数，但是它不能表示&＃39;1970-01-01 00:00:00&＃39;，因为这表示从纪元开始的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)

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

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

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

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

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

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位字符串&＃39;1901&＃39; 到 &＃39;2155&＃39;；

（2）4位数字1901 到 2155；

（3）1或者2位字符串&＃39;0&＃39; 到 &＃39;99&＃39;，MySQL将&＃39;0&＃39;到&＃39;69&＃39;转化为2000到2069，将&＃39;70&＃39;到&＃39;99&＃39;转化为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值将产生错误。

总结如下：

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程笔记。