List:General Discussion« Previous MessageNext Message »
From:Craig \(OFT\) Weston Date:August 29 2007 6:03pm
Subject:RE: [MYSQL]Time formatting for cycle time.
View as plain text  
Peter, Baron and all,

            I think that I am almost there. Here's my query to return
cycle time in hours:minutes:seconds accounting for business hours. There
is some discussion as to when a ticket cycle time ends - for example if
a person works on a ticket at 10:00 at night and closes it, it should
end then, not at close of business that day. 

 

            Thank you for your help. This is more complex than I really
feel qualified for and you have really helped me.

 

Regards,

Craig

 

 

DELIMITER $$

 

DROP FUNCTION IF EXISTS `BizHoursTimeDiff` $$

 

CREATE DEFINER=`root`@`localhost` FUNCTION `BizHoursTimeDiff`( d1
DATETIME, d2 DATETIME ) RETURNS char(30) CHARSET latin1

    DETERMINISTIC

BEGIN

  DECLARE dow1, dow2, days, wknddays INT;

  DECLARE tdiff CHAR(20);

  SET dow1 = DAYOFWEEK(d1);

  SET dow2 = DAYOFWEEK(d2);

  set @dayEnd = (select time(`business_hours`.`Day_End`) from
`business_hours` limit 1);

  SET @dayStart = (select time(`business_hours`.`Day_Start`) from
`business_hours` limit 1);

 

set @d1 = if (TIME(d1) > @dayEnd,@dayEnd,d1);

 

 

set @d1 = if (TIME(d1) <

            @dayStart,@dayStart,d1);

 

 

#set @d2 = if (TIME(@d2) < @dayStart,@dayStart,@d2);

 

#set @d2 = if @dayEnd,@dayEnd,@d2);

 

  SET tdiff = TIMEDIFF( TIME(d2), TIME(d1) );

  SET days = DATEDIFF(d2,d1);

  SET wknddays = 2 * FLOOR( days / 7 ) +

                 IF( dow1 = 1 AND dow2 > 1,1,

                     IF( dow1 = 7 AND dow2 = 1, 1,

                         IF( dow1 > 1 AND dow1 > dow2, 2,

                             IF( dow1 < 7 AND dow2 = 7, 1, 0 )

                           )

                       )

                   );

  SET @tdiff = tdiff;

  SET days = FLOOR(days - wkndDays) - (IF( ASCII(tdiff) = 45, 1, 0 ) +
(SELECT count(*)  FROM `holidays` WHERE `holidays`.`date` BETWEEN d1 AND
d2 AND WEEKDAY(`Holidays`.`date`)<5));

  SET tdiff = IF( ASCII(tdiff) = 45, TIMEDIFF( '24:00:00',
SUBSTRING(tdiff,2)), TIMEDIFF( tdiff, '00:00:00' ));

 

  SET @hr = days * left((@dayEnd - @dayStart),2) + left(tdiff,2);

  SET @min = mid(tdiff,4,2);

  SET @sec = mid(tdiff,7,2);

 

  RETURN concat_ws(':',@hr,@min,@sec);

 

   END $$

 

DELIMITER ;
--------------------------------------------------------
This e-mail, including any attachments, may be confidential, privileged or otherwise
legally protected. It is intended only for the addressee. If you received this e-mail in
error or from someone who was not authorized to send it to you, do not disseminate, copy
or otherwise use this e-mail or its attachments.  Please notify the sender immediately by
reply e-mail and delete the e-mail from your system.

Thread
[MYSQL]Time formatting for cycle time.Craig \(OFT\) Weston27 Aug
  • Can't start mysql Feliks Shvartsburd28 Aug
  • Re: [MYSQL]Time formatting for cycle time.Peter Brawley28 Aug
    • RE: [MYSQL]Time formatting for cycle time.OFT)28 Aug
      • Re: [MYSQL]Time formatting for cycle time.Baron Schwartz28 Aug
        • RE: [MYSQL]Time formatting for cycle time.Craig \(OFT\) Weston28 Aug
          • Re: [MYSQL]Time formatting for cycle time.Peter Brawley28 Aug
      • RE: [MYSQL]Time formatting for cycle time.Craig \(OFT\) Weston28 Aug
RE: [MYSQL]Time formatting for cycle time.Craig \(OFT\) Weston29 Aug