MYSQL-计算两个时间的时间差和工作日差

本是古典何须时尚 2022-12-07 11:40 540阅读 0赞

### 文章目录 ###

*  MYSQL-计算两个时间的时间差和工作日差
 *   *  1. 创建测试数据
     *  2. 计算时间差
     *  3. 计算工作日差

# 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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center]

## 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');`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/7b11ae2dea2b498e8c9bfa91bc36fd56.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNjUyMjA5OQ_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/b1ce91125f1f48928b4ae3522843258c.png