MySQL查询来计算同一行中两个单元格之间的工作日

Question

我有以下表格:

CREATE TABLE `document` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `systemReceivedTime` BIGINT(20) NOT NULL DEFAULT '0',
    `finishTime` BIGINT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)

一行:

systemReceivedTime 1360747818002 // > 2013-02-13 11:30:18
finishTime         1362995961805 // > 2013-03-11 11:59:22

我需要的是一种方法来计算两个日期之间的工作日数。

要正确显示日期，请使用以下select:

from_unixtime(systemreceivedtime/1000) ,
if(finishtime=0,null,from_unixtime(finishtime/1000))

Answer 1

SELECT 5 * (DATEDIFF(FROM_UNIXTIME(finishTime/1000), FROM_UNIXTIME(systemReceivedTime/1000)) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(FROM_UNIXTIME(systemReceivedTime/1000)) + WEEKDAY(FROM_UNIXTIME(finishTime/1000)) + 1, 1)x FROM document;

Answer 2

我有作为解决方案的变量。你可以把更多的公式放在一起，但我认为这样更容易读懂。

SELECT
    calc.id,
    calc.work_dates
FROM
    (
        SELECT
            d.id,
            @from :=from_unixtime(d.systemreceivedtime/1000)        AS dfrom,
            @to := from_unixtime(d.finishtime/1000)                 AS dto,
            -- Caclulate the Diffrent between both dates
            @ddiff := DATEDIFF(@to, @from)          AS ddiff,
            -- get the weekday of the startdate
            @wd := WEEKDAY(@from)                       AS weekdays,
            -- calculate how many days are from start of week until enddate
            @d1 := @ddiff + @wd                         AS days_from_week_start,
            -- calculate how many full week this is
            @fullweeks := @d1 DIV 7                     AS full_weeeks,
            -- calculate how many workdays are not in a fullweek
            @restdays := LEAST(MOD(@d1, 7), 5)      As rest_working_days,
            -- Weeks * 5 Workdays + rest_workdays - days_from_week_start
            @fullweeks * 5 + @restdays - @wd            AS work_dates
        FROM
            document AS d
    ) AS calc