MySQL has several functions for dealing with date and time values, many of
which exist only as of 4.1.x. Which ones are available to you depend on
your version. Please read:
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
Actually, I believe that the formula you wrote is almost the statement you
will need. What I think you are trying to do is to compute the % ELAPSED
time since the start of the project as compared to the SCHEDULED time.
That formula would look similar to (but not exactly like):
(End_Date_Actual - Start_Date_Actual)/(End_Date_Scheduled -
Start_Date_Scheduled) * 100
You want the actual elapsed time (expressed in convenient units, you
suggested seconds) divided by the scheduled elapsed time (expressed in the
same units) then multiplied by 100 to give you a percentage value. Using
only the functions available pre-4.1 that could look like (forgive the
wrapping):
(Cast(UNIX_TIMESTAMP(End_Date_Actual) as SIGNED) -
CAST(UNIX_TIMESTAMP(Start_Date_Actual) as
SIGNED))/(CAST(UNIX_TIMESTAMP(End_Date_Scheduled) AS SIGNED) -
CAST(UNIX_TIMESTAMP(Start_Date_Scheduled) AS SIGNED) * 100
The other option is to get the 4 starting and ending date values and do
the arithmetic in your programming language, instead. Go with whichever is
easiest and works best for you.
HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Viswanatha Rao" <[EMAIL PROTECTED]> wrote on 08/24/2004 03:06:13 PM:
> I am selecting two columns each of type DateTime from a mysql table from
> perl.
>
> my $sth = $dbh->prepare("SELECT End_Date_Scheduled, End_Date_Actual FROM
> Table1 WHERE ID='10'" );
> $sth->execute();
> while (my $ref = $sth->fetchrow_hashref()) {
> print"Found: end date scheduled = $ref->{'End_Date_Scheduled'}, end
> date actual = $ref->{'End_Date_Actual'}\n";
> }
> $sth->finish();
>
> It works.
>
> However, I need to find the % ratio = ((end_date_scheduled -
> end_date_actual)/end_date_scheduled) * 100
>
> To do this I need the DateTime values in seconds. Can someone help me
> with this?
>
>
>
> Best Regards
> Vishwa Rao
>
>
>
