Gossamer Forum
Home : Products : DBMan : Customization :

Calc total hours from Dtae and Time

Quote Reply
Calc total hours from Dtae and Time
Hi,

This might have been covered before. I have two fields in my DB which are date fields with hour and mins

eg.

Field1 = 16-Oct-2003 16:00

Field 2 = 17-Oct-2003 02:00

field 3 = total hrs worked: 10

What i'm trying to do is calc the total hrs and have this put into field 3 = Total hrs

help pls.....
Quote Reply
Re: [cwilcox] Calc total hours from Dtae and Time In reply to
I dont' have a specific thread reference but have you by any chance searched the FAQ noted below under both "Dates" and "Calculations"?

You could also search the forum for "total hours" and see if you can find a solution. I'm pretty sure this has been asked before.

Unoffical DBMan FAQ

http://creativecomputingweb.com/dbman/index.shtml/
Quote Reply
Re: [LoisC] Calc total hours from Dtae and Time In reply to
Hi LoisC,

Thanks for you quick reply. I searched where you suggested by no luck.

Is there a quick solution to this request..

Thanks heaps for the help..
Quote Reply
Re: [cwilcox] Calc total hours from Dtae and Time In reply to
Hi Friend,

Here is what I did with my problem, may be you can gain from this:

1. In index.pl or your index.cgi, I have this 2 sub.

Code:


sub get_date2 {
# --------------------------------------------------------
# Returns the date in the format "Mmmm dd, yyyy hh:mm ".
# Warning: If you change the default format, you must also modify the &date_to_unix
# subroutine below which converts your date format into a unix time in seconds for sorting
# purposes.
my ($time) = @_;
($time) || ($time = time());

my ($sec, $min, $hour, $day, $mon, $year, $dweek, $dyear, $daylight) = localtime($time);

($day < 10) and ($day = "0$day");

$mon += 1;
($mon < 10) and ($mon = "0$mon");

$year = $year + 1900;

if ($min < 10) {
$min = "0" . $min;
}

return "$mon\/$day\/$year $hour:$min:$sec";


}


sub date_to_unix2()
{
my ($mon,$day,$year,$hour,$min) =
$_[0] =~ /(\d+)\/(\d+)\/(\d+)\s(\d+):(\d+)/;
return undef unless ($day and $mon and $year);
return timelocal(0,$min,$hour,$day,$mon-1,$year-1900);
}




Then in my html.pl

I will add

Code:


# Calculate Down Time if we recived part.
my $epstart = (&date_to_unix2($rec{'Field1'}));

my $epreceive = (&date_to_unix2($rec{'Field2'}));

$diff = ($epreceive - $epstart);
$seconds = $diff % 60;
$diff = ($diff - $seconds) / 60;
$minutes = $diff % 60;
$diff = ($diff - $minutes) / 60;

$hours = $diff % 24;

$diff = ($diff - $hours) / 24;
$days = $diff % 7;

$totalday = $diff;

$weeks = ($diff - $days) / 7;

print qq|
<TD bgcolor=d3d3d3>$rec{'Field1'}</TD><TD bgcolor=d3d3d3>$rec{'Field2'}&nbsp;</TD><TD bgcolor=d3d3d3> $totalday days $hours:$minutes</TD>|;


Just look at this and you can do yours easily. OOp, remember to change the time format to the corect way of your style. Mine is mm/dd/yyyy

Good luck,Cool