From: Craig (OFT) Weston
Date: August 28 2007 12:17pm
Subject: RE: [MYSQL]Time formatting for cycle time.
List-Archive: http://lists.mysql.com/mysql/208758
Message-Id: <E9C66DE22474F54D9152AD9B4191E9BC01BB39A9@EXCNYSM0A1AM.nysemail.nyenet>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C7E96D.5C659524"

------_=_NextPart_001_01C7E96D.5C659524
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Wow, thanks. Lots to think about.

=20

________________________________

From: Peter Brawley [mailto:peter.brawley@stripped]=20
Sent: Monday, August 27, 2007 10:18 PM
To: Weston, Craig (OFT)
Cc: mysql@stripped
Subject: Re: [MYSQL]Time formatting for cycle time.

=20

Craig,



>I am working on Martin Minka's date diff function as found
>at  http://forge.mysql.com/snippets/view.php?id=3D56. 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 =3D '2007-1-1';
SET @d2 =3D '2007-3-31';
SET @dow1 =3D DAYOFWEEK(@d1);
SET @dow2 =3D DAYOFWEEK(@d2);
SET @days =3D DATEDIFF(@d2,@d1);
SET @wknddays =3D 2 * FLOOR( @days / 7 ) +
                IF( @dow1 =3D 1 AND @dow2 > 1, 1,

                    IF( @dow1 =3D 7 AND @dow2 =3D 1, 1,              =20
                        IF( @dow1 > 1 AND @dow1 > @dow2, 2,      =20
                            if( @dow1 < 7 AND @dow2 =3D 7, 1, 0 )  =20
                          )
                      )
                );
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 =3D '2007-1-1 00:00:00';
SET @d2 =3D '2007-3-31 12:00:00';
SET @tdiff =3D TIMEDIFF( TIME(@d1), TIME(@d2) );
SET @dow1 =3D DAYOFWEEK(@d1);
SET @dow2 =3D DAYOFWEEK(@d2);
SET @days =3D DATEDIFF(@d2,@d1);
SET @wknddays =3D 2 * FLOOR( @days / 7 ) +
                IF( @dow1 =3D 1 AND @dow2 > 1, 1,

                    IF( @dow1 =3D 7 AND @dow2 =3D 1, 1,              =20
                        IF( @dow1 > 1 AND @dow1 > @dow2, 2,      =20
                            IF( @dow1 < 7 AND @dow2 =3D 7, 1, 0 )  =20
                          )
                      )
                  );
SET @days =3D FLOOR(@days - @wkndDays) - IF( @tdiff < 0, 1, 0 );
SET @tdiff =3D IF( ASCII(@tdiff) =3D 45, SUBSTRING(@tdiff,2), TIMEDIFF(
'24:00:00', @tdiff ));
SELECT CONCAT( @days, ' days ', @tdiff );

PB

-----

Weston, Craig (OFT) wrote:=20

Hello all,=20
=20
            I am working on Martin Minka's date diff function as found
at  http://forge.mysql.com/snippets/view.php?id=3D56. 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)
=20
=20
=20
=20
=20
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.=20
=20
=20
=20
My long term goal is to identify the business hours(minutes, seconds,
whatever) between two dates, taking into account weekends, holidays, and
business hours.
=20
=20
=20
Thanks,
=20
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.
=20
 =20
=20



________________________________



=20
No virus found in this incoming message.
Checked by AVG Free Edition.=20
Version: 7.5.484 / Virus Database: 269.12.9/975 - Release Date:
8/26/2007 9:34 PM
 =20

------_=_NextPart_001_01C7E96D.5C659524--