mysql数据库技巧

1.日期增加半小时

select startTime,ADDDATE(startTime,interval 30 minute) from tbl_shift_hc;

2.存储过程

DROP PROCEDURE IF EXISTS `proc_test`;
DELIMITER ;;
CREATE PROCEDURE `proc_test`(IN _occupancy_weekday double,IN _ou varchar(100),IN _bTime Date,In _eTime Date,OUT _time_len NUMERIC(50,10))
    COMMENT '注解'
BEGIN
			DECLARE break_duration NUMERIC(50,10);-- NPT Break Duration字段数据合计
			DECLARE person_num int;-- 总人数
			
      select convert(sum(TIME_TO_SEC(breakDuration)),decimal(50,10))/convert(86400,decimal(50,10)), 
							count(*)
							into break_duration,person_num from tbl_shift_npt where ou=_ou and onlineDate >=_bTime and onlineDate<=_eTime;
			
			
			set _time_len = break_duration;
		END
;;
DELIMITER ;

调用方法

call proc_test(0.98,'US',now(),now(),@time_len);
select @time_len;

说明：

1.参数:IN：输入参数；OUT：输出参数。

2.除法要保证精度。convert(0.882222,decimal(50,10))/convert(0.233333333333,decimal(50,10))