???????????????????????????????????????￡?
-- ???????
DROP FUNCTION IF EXISTS calculateWorkingTime;
set @workStartTime='09:30:00';
set @workEndTime='18:30:00';
-- ????????
--/
CREATE FUNCTION calculateWorkingTime(startDate datetime??endDate datetime)
RETURNS decimal(32??4)
BEGIN
DECLARE intnum int(255) DEFAULT 0;
DECLARE decimalnum decimal(32??4) DEFAULT 0.000;
DECLARE workStartTimeHour decimal(32??4) DEFAULT 0.000;
DECLARE workEndTimeHour decimal(32??4) DEFAULT 0.000;
DECLARE currentDay int(10) DEFAULT 0;
DECLARE tempTimeHour decimal(32??4) DEFAULT 0.000;
-- DECLARE temp varchar(2048) DEFAULT '';
-- deal starttime and endtime is nonworkdays
SET startDate = (CASE WHEN weekday(startDate)=5 THEN concat(date(timestampadd(day??2??startDate))??' '??@workStartTime) WHEN weekday(startDate)=6 THEN concat(date(timestampadd(day??1??startDate))??' '??@workStartTime) ELSE startDate END);
SET endDate = (CASE WHEN weekday(endDate)=5 THEN concat(date(timestampadd(day??-1??endDate))??' '??@workEndTime) WHEN weekday(endDate)=6 THEN concat(date(timestampadd(day??-2??endDate))??' '??@workEndTime) ELSE endDate END);
--  SET temp = concat(temp??' '??startDate??';'??endDate);
if startDate < endDate then
-- deal starttime and endtime is nonworktime
if time(startDate)<=@workStartTime THEN
SET startDate = concat(date(startDate)??' '?? @workStartTime);
elseif date(startDate) < date(endDate) and time(startDate)>@workEndTime then
SET startDate = concat(date(date_add(startDate?? interval 1 day))??' '??@workStartTime);
end if;
if time(endDate)>=@workEndTime then
SET endDate = concat(date(endDate)??' '??@workEndTime);
elseif date(startDate) < date(endDate) and time(endDate)<@workStartTime then
SET endDate = concat(date(date_add(endDate?? interval -1 day))??' '??@workEndTime);
end if;
-- calculate time diff
SET decimalnum = (minute(endDate)*60+second(endDate)-minute(startDate)*60-second(startDate))/3600;
end if;
-- calculate work time second
SET workStartTimeHour = hour(@workStartTime)+minute(@workStartTime)/60+second(@workStartTime)/3600;
SET workEndTimeHour = hour(@workEndTime)+minute(@workEndTime)/60+second(@workEndTime)/3600;
--  WHILE (floor((unix_timestamp(endDate) - unix_timestamp(startDate))/3600) > 0) DO
WHILE ((floor(unix_timestamp(endDate)/3600) - floor(unix_timestamp(startDate)/3600)) > 0) DO
SET tempTimeHour = hour(startDate)+minute(startDate)/60+second(startDate)/3600;
if   workStartTimeHour <= tempTimeHour and tempTimeHour < workEndTimeHour then
--   SET temp = concat(temp??' '??tempTimeHour??';');
SET intnum = (CASE WHEN weekday(startDate)=5 or weekday(startDate)=6 then intnum ELSE intnum+1 END);
end if;
SET startDate = timestampadd(hour??1??startDate);
END WHILE;
SET decimalnum = intnum + decimalnum;
-- concat(decimalnum??';'??workStartTimeHour??' '??workEndTimeHour??' '??intnum??';;;'??temp);
RETURN decimalnum;
END
/
-- select calculateWorkingTime('2017-02-17 07:30:00'??'2017-02-21 17:39:00');
select transport_id??create_at1??create_at2??create_at3?? calculateWorkingTime(create_at1??create_at2)?? calculateWorkingTime(create_at2??create_at3)??calculateWorkingTime(create_at1??create_at3) from newTable;
select transport_id??calculateWorkingTime(create_at1??create_at2) from newTable;
?????????????????????????????д???????????д??????????????????????????к???????????
??????????????????????????????????????????????????SQL??????????????Щ????????????????????????????????????????????????????????????????????????????????????   ?????????????????????????????????????????