MySQL有关时间的函数

• 记录
• 总结
• 补充

MySQL datetime、date、time、str之间的转化与比较_nacrt的博客-CSDN博客_mysql todatetime

记录

获取时间、格式化、差值比对...

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'),
  str_to_date('2019-04-25 08:50:00', '%Y-%m-%d %T'),
  DATE('2019-04-25 08:50:00'),
  TIME ('2019-04-25 08:50:00'),
  
  -- 日期比较
  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 更多日期和时间相关函数请参考：https://www.mysqlzh.com/doc/118.html

总结

获取日期

读取系统变量

• CURRENT_TIMESTAMP：日期时间
• CURRENT_DATE：日期
• CURRENT_TIME：时间

通过函数获取时间：

• CURRENT_TIMESTAMP()、NOW()：日期时间(NOW()更常用)
• sysdate()：当前时间
• CURRENT_DATE()、CURDATE()：日期（两者同义）
• CURRENT_TIME()、CURTIME()：时间（两者同义，和NOW()行为一样）
• UTC_TIMESTAMP()、UTC_DATE()、UTC_TIME()：UTC时间（不常用）
• UNIX_TIMESTAMP()、UNIX_TIMESTAMP(now())：时间戳

格式化（类型互转）

%T等于%H:%i:%s，是简写形式。日期部分没有简写形式。

日期转字符串

• FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y-%m-%d %H:%i:%s')：时间戳格式化

• DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s')：时间格式化

• DATE('2019-04-25 08:50:00')：格式：'%Y-%m-%d'

• TIME ('2019-04-25 08:50:00')：格式：'%H:%i:%s'

字符串转日期

• str_to_date('2019-04-25 08:50:00', '%Y-%m-%d %H:%i:%s')
• str_to_date('2019-04-25 08:50:00', '%Y-%m-%d %T')：

时间增减

/* 对日期进行 加减（两个函数记一个即可）
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');

补充

NOW()和sysdate()区别

NOW()和sysdate()区别

MySql获取时间都是语句执行开始时的日期和时间，只有sysdate()是特殊的，是调用函数时的时间

NOW() 和 SYSDATE() 的主要区别在于它们何时计算时间：

特性	NOW()	SYSDATE()
返回值	语句执行开始时的日期和时间	调用函数时的日期和时间
语句内行为	在语句内值保持不变	在语句执行期间，值可能发生变化

时区设置

时区设置

临时设置

修改mysql全局时区为北京时间，即我们所在的东8区

set global time_zone = '+8:00';

修改当前会话时区

set time_zone = '+8:00'; 

刷新配置

flush privileges;

永久生效

修改mysql配置文件，在[mysqld]区域中加上：

default-time_zone = '+8:00'