List:General Discussion« Previous MessageNext Message »
From:Craig \(OFT\) Weston Date:August 28 2007 8:10pm
Subject:RE: [MYSQL]Time formatting for cycle time.
View as plain text  
Ok then,

Now I have a result set so very close to where I want to be. The query
thus far: 

mysql> SET @d1 = '2007-02-01 18:24:04';# Start date
SET @d2 = '2007-2-28 23:05:40';# End date

set @wkldays = (select WorkDayDiff`(@d2,@d1)-1);
Set @wkldays2 = if(@wkldays < 0,1,0);
set @Day_End = (select `business_hours`.`Day_End` from
`resource_data`.`business_hours` limit 1);
set @Day_Start = (select  `business_hours`.`Day_Start` from
`resource_data`.`business_hours` limit 1);

Set @t1 = (IF((HOUR(@d1))<@Day_Start,7,IF((HOUR(@d1))>@Day_End
,18,HOUR(@d1))))+(IF((HOUR(@d1))<@Day_Start,0,IF((HOUR(@d1))>@Day_End
,0,MINUTE(@d1)))/60)+(IF((HOUR(@d1))<@Day_Start,0,IF((HOUR(@d1))>@Day_En
d ,0,SECOND(@d1)))/3600);

Set @t2 = (IF((HOUR(@d2))<@Day_Start,7,IF((HOUR(@d2))>@Day_End
,18,HOUR(@d2))))+(IF((HOUR(@d2))<@Day_Start,0,IF((HOUR(@d2))>@Day_End
,0,MINUTE(@d2)))/60)+(IF((HOUR(@d2))<@Day_Start,0,IF((HOUR(@d2))>@Day_En
d ,0,SECOND(@d2)))/3600);

set @tdif = IF(@t1 > @t2,((22 - @t1) + (@t2 - 11)),@t2 - @t1);

select
@wkldays,
@t1,
@t2,
 @wkldays2,
@tdif, 
 ((@wkldays+@wkldays2)*11)+@tdif 'hours';

With the results: 



+----------+-----+-----+-----------+------- 
| @wkldays | @t1 | @t2 | @wkldays2 | @tdif  
+----------+-----+-----+-----------+------- 
| 17       | 18  | 18  | 0         | 0      
+----------+-----+-----+-----------+------- 

+------------------------------------+
| hours                              |
+------------------------------------+
| 187.000000000000000000000000000000 |
+------------------------------------+


My question now, is does anyone know how I could alter this query to get
precision in the hours? The idea is to get at least to the minute
resolution. I tried ((@wkldays+@wkldays2)*11)+@tdif + 0.0000, but I have
few brain cells not on strike. 

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.

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