Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210

    Unanswered: How To: Datediff skipping weekends and holidays

    New to MySQL (normally I use SQL Server). I'm sure its a simple matter for a seasoned MySQLer - I'm still struggling with the picky syntax quirks and supported feature differences - Ugg.

    It took me hours to work this out so I thought I'd share.

    Note: I tried doing an algorithm but in the end just decided to create a simple Calendar Table. That way I could do holidays.

    Problem: I needed a user function that would measure the work-days between two dates, not inclusive of the final date.

    Example: Order is due 10/1/2013. Today is 10/2/2013. It is now 1-day-late. Function returns "1".

    Comments welcome. It may not be the most elegant code - it runs fast enough for my use. I don't notice any noticeable overhead on a query with several thousand rows - but I'm always ready to learn where I could tweak a little more or refine things. I'm always amazed at the elegant solutions presented here.

    1. Create tbCalendar. Create with index that includes both fields.
    Code:
    CREATE TABLE mydb.tbCalendar (
      CalDate date NOT NULL,
      isWorkDay bit(1) DEFAULT NULL,
      UNIQUE INDEX UK_tbCalendar (CalDate, isWorkDay),
      UNIQUE INDEX UK_tbCalendar_CalDate (CalDate)
    )
    ENGINE = MYISAM
    AVG_ROW_LENGTH = 7
    CHARACTER SET utf8
    COLLATE utf8_general_ci;
    2. Populate it with weekends and a handy "isWorkDay" bit. This goes from 1998 thru 2052.
    Code:
    CREATE DEFINER = 'sa'@'192.168.1.%'
    PROCEDURE mydb.procedure1()
    BEGIN
    
      DECLARE v1 INT DEFAULT 1;
      DECLARE d DATE;
      set d = '1998-01-01';
      TRUNCATE TABLE tbCalendar;
    
      WHILE v1 < 20000 DO
        INSERT INTO tbCalendar (CalDate,CalDayOfWeek, isWorkDay) 
          select  date_add(d,INTERVAL v1 DAY),
                  CASE when DAYOFWEEK(date_add(d,INTERVAL v1 DAY)) IN (1,7) THEN
                    0 ELSE 1 END;
        SET v1 = v1 + 1;
      END WHILE;
    END
    Code:
    call mydb.procedure1
    Of course; manually set "isWorkDay" to zero for holidays.


    3. Create function.
    Code:
    CREATE DEFINER = 'sa'@'192.168.1.%'
    FUNCTION mydb.fn_WorkDaysDiff(StartDate date, EndDate date)
      RETURNS int(11)
    BEGIN
    
    DECLARE answer int;
    
    IF StartDate > EndDate THEN
      SELECT (COALESCE(COUNT(*),0) - 1) * -1 INTO answer FROM saws_cent.tbCalendar WHERE
      caldate BETWEEN EndDate AND StartDate  AND isWorkDay = 1 LIMIT 1;
    ELSE
      SELECT COALESCE(COUNT(*),0) - 1 INTO answer FROM saws_cent.tbCalendar WHERE
      caldate BETWEEN StartDate AND EndDate AND isWorkDay = 1 LIMIT 1;
    END IF;
    
    -- If they are both in the same weekend, it returns -1.
    IF answer = -1 then
      IF DAYOFWEEK(EndDate) IN (7,1) THEN SET answer = 0;
        END IF;
    end IF;
    
    RETURN answer;
    
    END
    Last edited by vich; 10-28-13 at 21:14. Reason: mydb

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Personally I would extend your calendar table with a field to signify whether or not the day is a weekend or not.

    As you say you're from a SQL Server background I will point you in the direction of [url=http://gvee.co.uk/files/sql/dbo.numbers%20&%20dbo.calendar.sql]my numbers and calendar script[/urll]. Note how this includes a few other helper columns that would make your task here much easier.

    Also, I probably wouldn't make it a function, instead I'd just make it a select statement (but you can if you want).

    I'll break down my solution a little to you.

    If you have 2 date parameters and we can't guarantee which one will be higher than the other I reckon this is a pretty simple way of normalising the result:
    Code:
    SELECT Min(boundary) As start_date
         , Max(boundary) As end_date
    FROM   (
            SELECT date1 As boundary
            UNION ALL
            SELECT date2 As boundary
           ) As boundaries
    Then using a variation on the calendar table script I linked to we can exclude weekends and holidays from our result with ease
    Code:
    SELECT Count(*)
    FROM   calendar
     CROSS
      JOIN (
            SELECT Min(boundary) As start_date
                 , Max(boundary) As end_date
            FROM   (
                    SELECT date1 As boundary
                    UNION ALL
                    SELECT date2 As boundary
                   ) As boundaries
           ) As dates
    WHERE  calendar.the_date BETWEEN dates.start_date AND dates.end_date
    AND    calendar.is_weekend = 0
    AND    calendar.is_holiday = 0
    Hope this helps
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Thanks for responding.

    Please help me understand how this would work as a query rather than a function?

    I have (to paraphrase) a query that looks something like this:

    Select OrderNum, PromiseDate, fn_OrderAge(PromiseDate,CURDATE()) AS DaysLate from myOrders

    DaysLate can go negative if the order is not due yet. EG: If it's due in 2 days from now then it's -2 days late. If it's due today, it's 0 days late. Ergo; you can't count Today as one of the day counts. The MySQL "datediff" function will never return a negative. This could make doing this as a linked sub-view crazy.

    Also; my query is a VIEW. Embedded subqueries are not allowed in MySQL Views (yes, amazingly!). SubViews are. That said; the MySQL optimizer falls apart if the subview has anything complex, particularly aggregates (max / min / count). (we're talking, falls flat on its face)

    Embedded functions are inherently performance drags but I just don't see any way around it in MySQL. Surprisingly; this one actually runs pretty fast.

    Note: I did notice that I should get rid of "BETWEEN" in favor of old fashioned >= and < statements. That will get rid of my -1 problem. As written, the query won't handle holidays properly; if the ending-date is a holiday (realistically; nobody in the history of the company has run a Production Report of any kind on a holiday). Still; it's messy to leave a bug in-tact.
    Last edited by vich; 10-29-13 at 14:20.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •