List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 28 2007 8:16pm
Subject:Re: [MYSQL]Time formatting for cycle time.
View as plain text  
Craig,

Right you are, here is a corrected func:

DROP FUNCTION IF EXISTS BizDateTimeDiff;
DELIMITER |
CREATE FUNCTION BizDateTimeDiff( d1 DATETIME, d2 DATETIME )
RETURNS CHAR(30)
DETERMINISTIC
BEGIN
  DECLARE dow1, dow2, days, wknddays INT;
  DECLARE tdiff CHAR(10);
  SET dow1 = DAYOFWEEK(d1);
  SET dow2 = DAYOFWEEK(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 );
  SET tdiff = IF( ASCII(tdiff) = 45, TIMEDIFF( '24:00:00', 
SUBSTRING(tdiff,2)), TIMEDIFF( tdiff, '00:00:00' ));
  RETURN CONCAT( days, ' days ', tdiff );
END;
|
DELIMITER ;
SELECT BizDateTimeDiff( '2007-1-1 00:00:00', '2007-3-31 00:00:00' ) AS 
dtdiff;
SELECT BizDateTimeDiff( '2007-1-1 11:00:00', '2007-3-31 00:00:00' ) AS 
dtdiff;
SELECT BizDateTimeDiff( '2007-1-1 12:00:00', '2007-3-31 13:00:00' ) AS 
dtdiff;
SELECT BizDateTimeDiff( '2007-1-1 00:00:00', '2007-3-31 11:00:00' ) AS 
dtdiff;

PB

-----

Weston, Craig (OFT) wrote:
> Ok, So, What I have come up with (so far) as a variant of Baron's query:
>
>
>
> SET @d1 = '2007-2-1 00:00:00';# Start date
> SET @d2 = '2007-2-28 23:59:59';# End date
> SET @tdiff = TIMEDIFF( TIME(@d1), TIME(@d2) );
> SET @dow1 = DAYOFWEEK(@d1);
> SET @dow2 = DAYOFWEEK(@d2);
> 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 @days2 = FLOOR(@days - @wkndDays) - (IF( @tdiff > 0, 1, 0 ) +
> (SELECT count(*)  FROM `resource_data`.`holidays` WHERE
> `resource_data`.`holidays`.`date` BETWEEN @d1 AND @d2));
> SET @tdiff = IF( ASCII(@tdiff) = 45, SUBSTRING(@tdiff,2), TIMEDIFF(
> '24:00:00', @tdiff ));
> SELECT CONCAT( @days2, ' days ', @tdiff ),
> @wknddays,
> @days,
> @days2,
> @tdiff,
> IF( @tdiff < 0, 1, 0 ),#Test value 1
> IF( @tdiff > 0, 1, 0 ),#Test value 2
> FLOOR(@days - @wkndDays),
> (SELECT count(*)  FROM `holidays` WHERE `holidays`.`date` BETWEEN @d1
> AND @d2);
>
>
>
> What I did was reverse the @tdiff equation to add a day for a positive
> @tdiff instead of subtracting it. My test month, February, has 2
> holidays in it. 
>
> The results:
>
> +------------------------------------+-----------+-------+ 
> | CONCAT( @days2, ' days ', @tdiff ) | @wknddays | @days |
> +------------------------------------+-----------+-------+
> | 17 days 23:59:59                   | 8         | 27    | 
> +------------------------------------+-----------+-------+ 
>
>
> --------+------------------------+------------------------+----------+
>  @days2 | IF( @tdiff < 0, 1, 0 ) | IF( @tdiff > 0, 1, 0 ) | holidays |
> --------+------------------------+------------------------+----------+
>  17     |                      0 |                      1 |        2 |
> --------+------------------------+------------------------+----------+
>
> This seems to account for the remainder better?
>
> Of course the original reason it was added was to take into account
> times that were earlier, which it does not seem to do?
>
> Thank you for your help!
> Craig
> --------------------------------------------------------
> 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