mysql自带的通过计算两个String类型的日期差、mysql事件。

select TIMESTAMPDIFF(HOUR ,‘2019-07-09 11:23:27’, ‘2019-07-19 11:23:27’) as a
SECOND 秒 SECONDS

MINUTE 分钟 MINUTES

HOUR 时间 HOURS

DAY 天 DAYS

MONTH 月 MONTHS

YEAR 年 YEARS

select concat((SELECT DATE_FORMAT(sitime,’%Y-%m-%d’) FROM t_signin where eid=1),’ ',(select begintime from t_worktime))

SELECT DATE_FORMAT(begintime,’%Y%m%d%H%i%s’) FROM t_worktime

SELECT DATE_FORMAT(sitime,’%H%i%s’) FROM t_signin

select concat((SELECT DATE_FORMAT(sitime,’%Y-%m-%d’) FROM t_signin where eid=1),’ ',(select begintime from t_worktime))

SELECT DATE_FORMAT((select concat((SELECT DATE_FORMAT(sitime,’%Y-%m-%d’) FROM t_signin where eid=1),’ ‘,(select begintime from t_worktime))),’%H%i%s’)

事件

SHOW VARIABLES LIKE ‘event_scheduler’; //查询事件开启状态

SET GLOBAL event_scheduler = 1 //1开启 2关闭

delimiter |
CREATE EVENT test4
ON SCHEDULE every 5 second
ON COMPLETION PRESERVE
DO BEGIN
insert into test(time) values(now());
END |
delimiter ;

delimiter |
CREATE EVENT test5
ON SCHEDULE EVERY 24 hour
ON COMPLETION PRESERVE
DO BEGIN
delete from test;
END |
delimiter ;