MySQL query to calculate workdays between two cells in the same row

Question

I have the following table:

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`)
)

a row:

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

what i need is a method to calculate the number of workdays between two dates.

to display the date properly use the following select:

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

score 0 · Accepted Answer · answered Mar 11 '13 at 13:20

0

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;

answered Mar 11 '13 at 13:20

Strawberry

33,750
13
40
57

yes but, a work colleague told me that this algorithm sometimes fails for some intervals. is there some other way or this is the only one? – Ciprian Gheorghite Mar 11 '13 at 13:24
I think this works. The 'mid' string is from the comments section rather than the core answer at dnagirl's link. It's slightly different. – Strawberry Mar 11 '13 at 13:33

score 0 · Answer 2 · answered Mar 11 '13 at 14:09

I have as solutions with variables. You can take mor formulas togeter, but i think its more readable like this.

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

MySQL query to calculate workdays between two cells in the same row

2 Answers2