水平不高,适合初学者看。
第一次写mysql的存储函数,但在网上找了很多存储函数的样式都在本地执行不了;我这个sql应该复制粘贴就能执行,初学者,可以当做样式来学习一下。
先说需求,业务需求就不说了,直接说数据库层面的需求:
以前表中有两个字段 start_time(以零点开始:2018-03-06 00:00:00)和end_time(24点结束:2018-03-09 23:59:59),是一个时间区间,现在要转换成一个text类型的字段sprint_days,将每天零点的毫秒数用“|”拼接起来。其中剔除掉周末。例如:1520265600000|1520352000000|1520438400000|1520524800000
需要注意的是,JAVA语言中的时间戳和mysql的时间戳是有区别的:
当前时间是 2014-08-04 10:42:55.204000
使用mysql时间戳函数UNIX_TIMESTAMP 返回的结果为: 1407120175.204000
使用Java时间戳函数返回的结果为 : 1407120379000
mysql时间戳计算方法是先计算2014-08-04 10:42:55 的时间戳,将该值除以10^3,然后加上后面毫秒作为返回结果
但通常我们在程序中用Java返回的时间戳更加普遍, 那如何把mysql时间戳转换成JAVA时间戳呢 ?
将mysql时间戳结果做如下计算:
小数点左边数据*1000+小数点右边的值 = Java时间戳
SQL公式如下:
MID(UNIX_TIMESTAMP(createTime),1,10)*1000+MID(UNIX_TIMESTAMP(createTime),12,6)
下边是源SQL:
-- 修改迭代有效日期存储函数
DROP FUNCTION IF EXISTS get_sprint_days;
DELIMITER //
CREATE FUNCTION get_sprint_days(start_time DATETIME,end_time DATETIME)
RETURNS VARCHAR(1000)
BEGIN
declare vaild_day DATETIME;
declare last_one DATETIME;
declare sprint_days varchar(1000) default '';
set end_time = date_add(date_add(end_time,interval 1 second),interval -1 day);
set vaild_day = start_time;
set last_one = end_time;
-- 因为要剔除周末,而最后一天后面是不跟‘|’的,所以比对时应该找出不是周末的最后一天
while date_format(last_one,'%w') in (6,0) DO
set last_one = date_add(last_one,INTERVAL -1 day);
end while;
-- 如果start_time 为周六,end_time 为周日,这种情况直接返回空值
if start_time >last_one then
RETURN sprint_days;
end if;
while vaild_day between start_time and end_time DO
if date_format(vaild_day,'%w') not in(6,0) then
set sprint_days = CONCAT(sprint_days,UNIX_TIMESTAMP(vaild_day)*1000);
if vaild_day not in(last_one) then
set sprint_days = concat(sprint_days,'|');
end if;
end if;
set vaild_day = date_add(vaild_day,interval 1 day);
end while;
RETURN sprint_days;
END//
DELIMITER ;