您可在储存程序中使用临时表格。

DELIMITER ;;
DROP PROCEDURE IF EXISTS ListHours ;;
CREATE PROCEDURE ListHours()
BEGIN
DECLARE curDT DATETIME;
DECLARE today DATETIME ;
DECLARE nextSaturday DATETIME;
DECLARE nextSunday DATETIME;
DECLARE iterDate DATETIME;
DECLARE iterDateTime DATETIME;
DECLARE iterBound DATETIME;
DECLARE resDate DATETIME;
DECLARE resTime DATETIME;
DECLARE delta INT;
DROP TABLE IF EXISTS tempNextWeek;
CREATE TEMPORARY TABLE IF NOT EXISTS tempNextWeek
(
    client_date VARCHAR(20),
    client_time VARCHAR(20)
);
DELETE FROM tempNextWeek;
SET curDT = NOW();
SET today = ADDTIME(SUBTIME(curDT , TIME(curDT)) ,  9:0:0 );
SET delta = 8 - DAYOFWEEK(today);  
SET  nextSunday = ADDDATE(today , INTERVAL delta DAY);
SET  nextSaturday = ADDTIME(nextSunday ,  6 0:0:0 );
-- select today , delta , nextSaturday , nextSunday ;
SET iterDate = nextSunday;
WHILE iterDate <= nextSaturday DO
    SET iterDateTime = iterDate;
    SET iterBound = ADDTIME(iterDateTime,  8:0:0 );
    WHILE iterDateTIme <= iterBound DO
        INSERT tempNextWeek (client_date, client_time) VALUE ( DATE_FORMAT(iterDateTime,  %Y-%m-%d ), DATE_FORMAT(iterDateTime,  %H:%i:%s ) );
        SET iterDateTime = ADDTIME(iterDateTime ,  1:0:0 );
    END WHILE;
    SET iterDate = ADDTIME(iterDate ,  1 0:0:0 );
END WHILE ;
SELECT * FROM tempNextWeek;
-- drop table if exists tempNextWeek;
END;;
DELIMITER ;

CALL ListHours();

你们需要建立一个表格,储存日期和时间价值。

CREATE TABLE calendarhours (caldaytime DATETIME);    

届时,你将需要建立一个储存的程序,以便沿用两个日期,并将时间表时间值插入表格。

DELIMITER $$    

CREATE DEFINER=`root`@`localhost` PROCEDURE `timesheetdays`(startdate DATETIME, enddate DATETIME)    
BEGIN    
    DECLARE tempdate DATETIME;    


    DELETE FROM `calendarhours`;    

    -- set the temp date to 9am of the start date     
    SET tempdate = DATE_ADD(DATE(startdate), INTERVAL  0 9  DAY_HOUR);     

    -- while the temp date is less than or equal to the end date, insert the date    
    -- into the temp table    
    WHILE ( tempdate <= enddate ) DO    
        BEGIN    
            -- insert temp date into temp table    
            INSERT INTO `calendarhours` (caldaytime) VALUES (tempdate);    
            -- increment temp date by an hour    
            SET tempdate = DATE_ADD(tempdate, INTERVAL  0 1  DAY_HOUR);    

            -- if the temp date is greater than 5 PM (17:00) then increment to the next day    
            IF TIMEDIFF(tempdate, DATE_ADD(DATE(tempdate), INTERVAL  0 17  DAY_HOUR)) > 0 THEN    
                BEGIN    
                    -- increment to the next day    
                    SET tempdate = DATE_ADD(DATE(tempdate), INTERVAL  1 9  DAY_HOUR);     
                    -- for business purposes, if the day is a Saturday or a Sunday increment     
                    -- until we reach Monday    
                    WHILE ( DAYNAME(tempdate) =  Saturday  OR DAYNAME(tempdate) =  Sunday  ) DO    
                        BEGIN    
                            SET tempdate = DATE_ADD(DATE(tempdate), INTERVAL  1 9  DAY_HOUR);     
                        END;    
                    END WHILE;    
                END;    
            END IF;    
        END;    
    END WHILE;    

    -- return all the inserted date and times    
    SELECT * FROM calendarhours ORDER BY caldaytime;    

END    

这一程序将持续到两个日期,从每天9天起,每天5分钟(17:00)结束。 当时间到下午8时,程序将持续到第二天,9时再次开始。

If you are doing a standard business week timesheet, then if the day is equal to Saturday or Sunday, it will increment until it reaches Monday.

为测试我使用了以下陈述:

CALL `timesheetdays`(NOW(), DATE_ADD(DATE(NOW()), INTERVAL  5 0  DAY_HOUR));    
SELECT * FROM `calendarhours`;    

This tests the procedure from today to 5 days from today and shows the hours as required. The first statement adds the records to the table and then returns the records, the second statement returns the records from the table.