MYSQL-計算兩個時間的時間差和作業日差

1. 創建測驗資料

create table time_table(id int PRIMARY KEY,start_time varchar(50),end_time varchar(50));
insert into time_table values(1,'2020-08-01 00:00:00','2020-09-01 00:00:00');
insert into time_table values(2,'2020-08-01 12:00:00','2020-09-01 13:00:00');
insert into time_table values(3,'2019-08-01 00:00:00','2020-09-01 00:00:00');
select * from time_table;

在這里插入圖片描述

2. 計算時間差

SELECT DATEDIFF(end_time,start_time) from time_table;    -- 相差多少天
SELECT TIMESTAMPDIFF(SECOND,start_time,end_time) from time_table;  -- 相差多少秒
SELECT TIMESTAMPDIFF(MINUTE,start_time,end_time) from time_table;  -- 相差多少分鐘
SELECT TIMESTAMPDIFF(HOUR,start_time,end_time) from time_table;    -- 相差多少小時
SELECT TIMESTAMPDIFF(DAY,start_time,end_time) from time_table;  -- 相差多少天
SELECT TIMESTAMPDIFF(MONTH,start_time,end_time) from time_table;  -- 相差多少個月
SELECT TIMESTAMPDIFF(YEAR,start_time,end_time) from time_table;  -- 相差多少年

SELECT '一年內第幾周',WEEK('2020-08-01');  -- 一年內第幾周-30
SELECT '年份',YEAR('2020-08-01');         -- 年份-2020
SELECT '月份',MONTH('2020-08-01');        -- 月份-8
SELECT '日期',DAY('2020-08-01');          -- 日期-1
SELECT '周六',DAYOFWEEK('2020-08-01');    -- 周六-7
SELECT '周日',DAYOFWEEK('2020-08-02');    -- 周日-1
SELECT '周一',DAYOFWEEK('2020-08-03');    -- 周一-2
SELECT '周二',DAYOFWEEK('2020-08-04');    -- 周二-3
SELECT '周三',DAYOFWEEK('2020-08-05');    -- 周三-4
SELECT '周四',DAYOFWEEK('2020-08-06');    -- 周四-5
SELECT '周五',DAYOFWEEK('2020-08-07');    -- 周五-6

3. 計算作業日差

DELIMITER $$
DROP FUNCTION IF EXISTS workdaynum $$
CREATE FUNCTION workdaynum(datefrom DATE,dateto DATE) RETURNS INT(20)
    NO SQL
BEGIN
	DECLARE days INT DEFAULT 1;    	-- 宣告一個變數并初始化值
	DECLARE A INT DEFAULT 0;
	DECLARE B INT DEFAULT 0;
	IF (datefrom > dateto OR YEAR(datefrom) != YEAR(dateto)) THEN             -- datefrom必須小于dateto，且同年
	   RETURN -1;
	END IF;
	
	-- 周：日一二三四五六
	-- 對應一周的第幾天：1234567
	
	-- 相差多少天
	SET days =    
	(CASE
		-- 同一周計算
	    WHEN WEEK(dateto)-WEEK(datefrom) = 0 THEN DAYOFWEEK(dateto) - DAYOFWEEK(datefrom) + 1
		-(CASE
			WHEN (DAYOFWEEK(datefrom) > 1 AND DAYOFWEEK(dateto) < 7) THEN 0    -- 頭尾都是作業日，大減小再加1
			WHEN (DAYOFWEEK(datefrom) = 1 AND DAYOFWEEK(dateto) = 7) THEN 2     -- 頭尾都是周末，大減小再加1再減2
			ELSE 1                                                             -- 其他值為1
			END)
		-- 不是同一周，分成三段計算：開始周+中間周+結束周
		ELSE (WEEK(dateto)-WEEK(datefrom)-1) * 5                       -- 中間周，每周5天，         
		+(CASE
		    WHEN DAYOFWEEK(datefrom) = 1 THEN 5                       -- 開始周，開始時間在周日算5天，在周六算0天，
			WHEN DAYOFWEEK(datefrom) = 7 THEN 0
		    ELSE 7 - DAYOFWEEK(datefrom)
			END)
		+(CASE
		    WHEN DAYOFWEEK(dateto) = 1 THEN 0                         -- 結束周，結束時間在周日算0天，在周六算5天，
			WHEN DAYOFWEEK(dateto) = 7 THEN 5
			ELSE DAYOFWEEK(dateto) - 1
			END)
	END);
	RETURN days;
END $$
DELIMITER ;

測驗：SELECT workdaynum('2020-08-01','2020-09-01');
在這里插入圖片描述

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/72772.html

標籤：其他

上一篇：mybatis原理分析(一)---JDBC

下一篇：無語，我差點被面試官懟壞了，又給我問到MySQL索引