List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:August 28 2007 12:29pm
Subject:Re: [MYSQL]Time formatting for cycle time.
View as plain text  
Hi Peter,

It would be great if you'd post this snippet at the forge too.  More is 
better :-)

Baron

Weston, Craig (OFT) wrote:
> Wow, thanks. Lots to think about.
> 
>  
> 
> ________________________________
> 
> From: Peter Brawley [mailto:peter.brawley@stripped] 
> Sent: Monday, August 27, 2007 10:18 PM
> To: Weston, Craig (OFT)
> Cc: mysql@stripped
> Subject: Re: [MYSQL]Time formatting for cycle time.
> 
>  
> 
> Craig,
> 
> 
> 
>> I am working on Martin Minka's date diff function as found
>> at  http://forge.mysql.com/snippets/view.php?id=56. It is a beautiful
>> thing. However, I am trying to alter it or identify a similar function
>> that instead of giving me the number of days between two dates it
>> returns the number of workday hours:minutes between two datetimes, or
>> some other date differential (such as an exact number of days between
>> two dates with remainder)
> 
> Here's a logically equivalent datediff calc, mebbe slightly simpler:
> 
> SET @d1 = '2007-1-1';
> SET @d2 = '2007-3-31';
> 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 )   
>                           )
>                       )
>                 );
> SELECT FLOOR(@days-@wkndDays) AS BizDays;
> 
> To include time in the difference, you could adopt as a return
> convention a string format like 'N days hh:mm:ss', where N is the date
> difference calculated above, minus one if the time portion of d1 is
> later than than that of d2. Something like this:
> 
> SET @d1 = '2007-1-1 00:00:00';
> SET @d2 = '2007-3-31 12:00:00';
> 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 @days = FLOOR(@days - @wkndDays) - IF( @tdiff < 0, 1, 0 );
> SET @tdiff = IF( ASCII(@tdiff) = 45, SUBSTRING(@tdiff,2), TIMEDIFF(
> '24:00:00', @tdiff ));
> SELECT CONCAT( @days, ' days ', @tdiff );
> 
> PB
> 
> -----
> 
> Weston, Craig (OFT) wrote: 
> 
> Hello all, 
>  
>             I am working on Martin Minka's date diff function as found
> at  http://forge.mysql.com/snippets/view.php?id=56. It is a beautiful
> thing. However, I am trying to alter it or identify a similar function
> that instead of giving me the number of days between two dates it
> returns the number of workday hours:minutes between two datetimes, or
> some other date differential (such as an exact number of days between
> two dates with remainder)
>  
>  
>  
>  
>  
> I hate asking open ended questions, but can anyone give me any hints as
> to how to make this conversion? I may be able to figure it out... In a
> few weeks. 
>  
>  
>  
> My long term goal is to identify the business hours(minutes, seconds,
> whatever) between two dates, taking into account weekends, holidays, and
> business hours.
>  
>  
>  
> Thanks,
>  
> 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.
>  
>   
>  
> 
> 
> 
> ________________________________
> 
> 
> 
>  
> No virus found in this incoming message.
> Checked by AVG Free Edition. 
> Version: 7.5.484 / Virus Database: 269.12.9/975 - Release Date:
> 8/26/2007 9:34 PM
>   
> 

-- 
Baron Schwartz
Xaprb LLC
http://www.xaprb.com/
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