Caluculate date time difference....

[lstephane]

I need to calculate time between two time fields but it is rounding the final value ?!?!?

Anybody can help me ?


Stéphane Lambert, Analyst/Programmer - BI Specialist
Info Quest (IQ), Data Warehouse System
McKesson Canada Corporation

EMail: stephane.lambert@mckesson.ca

 

[sleipnir214]

How are you doing it now?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[lstephane]

I'm doiing (TimeStart - TimeEnd)

where timestart & timeend are defined as TIME into my MySQL base...


Stéphane Lambert, Analyst/Programmer - BI Specialist
Info Quest (IQ), Data Warehouse System
McKesson Canada Corporation

EMail: stephane.lambert@mckesson.ca

 

[sleipnir214]

I'm sorry, I was not specific enough with my question.

Are you performing this calculation in your query, or in PHP?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[lstephane]

Into my PHP script...


Stéphane Lambert, Analyst/Programmer - BI Specialist
Info Quest (IQ), Data Warehouse System
McKesson Canada Corporation

EMail: stephane.lambert@mckesson.ca

 

[sleipnir214]

I understand that.

But are you:[ul][li]Fetching the two timedate stamps and trying to perform this subtraction using PHP functions, or[/li][li]are you performing the subtraction as part of a query using MySQL's functions[/li][/ul]?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[lstephane]

Hi,

Here is my code for this issue (it is not fully debugged):

===> BEGIN <===
?>
<table border="1" cellpadding="0" cellspacing="0" bordercolor="#111111" width="100%">
<tr>
<td bgcolor="#FF0000">
<p style="margin-left: 10"><b><font size="2" face="Verdana" color="#FFFFFF">
Titre</font></b></td>

<td align="center" bgcolor="#FF0000">
<p><b><font face="Verdana" size="2" color="#FFFFFF">
Temps</font></b></td>
</tr>
<?
$TempsCumul=0;

$dbQuery_cust = "SELECT distinct serv.customer_id ";
$dbQuery_cust .= "FROM asj_services serv, asj_dispatch dsp ";
$dbQuery_cust .= "WHERE serv.service_id = dsp.service_id ";

if ($date_deb!="&quot
{
$dbQuery_cust .= "AND serv.service_date >= $date_deb ";
}

if ($date_fin!="&quot
{
$dbQuery_cust .= "AND serv.service_date <= $date_fin ";
}
else
{
$dbQuery_cust .= "AND serv.service_date <= $today ";
}

$dbQuery_cust .= "ORDER BY 1 ASC";

$result_cust = mysql_query($dbQuery_cust) or die("Couldn't get file list"

while($row_cust = mysql_fetch_array($result_cust))
{
$HreVal = 0;

$Cust_ID = $row_cust["customer_id"];

$dbQuery_nm = "SELECT customer_name ";
$dbQuery_nm .= "FROM asj_customers ";
$dbQuery_nm .= "WHERE customer_id = $Cust_ID ";

$result_nm = mysql_query($dbQuery_nm) or die("Couldn't get file list"

$row_nm = mysql_fetch_array($result_nm);
?>
<tr>
<td valign="middle" bgcolor="#FFFFFF">
<p style="margin-left: 10; margin-right: 10">
<font face="Verdana" size="1">
<?php echo $row_nm["customer_name"]; ?>
</font>
</td>
<?
$dbQuery_sid = "SELECT distinct serv.service_id ";
$dbQuery_sid .= "FROM asj_services serv ";
$dbQuery_sid .= "WHERE serv.customer_id = $Cust_ID ";

if ($date_deb!="&quot
{
$dbQuery_sid .= "AND serv.service_date >= $date_deb ";
}

if ($date_fin!="&quot
{
$dbQuery_sid .= "AND serv.service_date <= $date_fin ";
}
else
{
$dbQuery_sid .= "AND serv.service_date <= $today ";
}

$dbQuery_sid .= "ORDER BY 1 ASC";

$result_sid = mysql_query($dbQuery_sid) or die("Couldn't get file list"

while($row_sid = mysql_fetch_array($result_sid))
{
$Serv_ID = $row_sid["service_id"];

$dbQuery_qtr = "SELECT distinct qtr_id ";
$dbQuery_qtr .= "FROM asj_quarts ";
$dbQuery_qtr .= "WHERE service_id = $Serv_ID ";

$result_qtr = mysql_query($dbQuery_qtr) or die("Couldn't get file list"

while($row_qtr = mysql_fetch_array($result_qtr))
{
$Qtr_ID = $row_qtr["qtr_id"];

$dbQuery_tot = "SELECT service_qtr_heure_deb, service_qtr_heure_fin ";
$dbQuery_tot .= "FROM asj_quarts ";
$dbQuery_tot .= "WHERE service_id = $Serv_ID ";
$dbQuery_tot .= "AND qtr_id = $Qtr_ID ";

$result_tot = mysql_query($dbQuery_tot) or die("Couldn't get file list"

$row_tot = mysql_fetch_array($result_tot);

$HreVal = $HreVal + ( ($row_tot["service_qtr_heure_fin"] - $row_tot["service_qtr_heure_deb"]) );
} // QTR
} // SID
?>

<td align="center" valign="middle" bgcolor="#FFFFFF">
<p>
<font face="Verdana" size="1">
<? printf ('%0.2f', $HreVal); ?> hres<br>
</font>
</td>
</tr>
<?
$TempsCumul=$TempsCumul+$HreVal;
}
?>
</table><br>

<font size="+1">Total des heures de service pour la sélection courante: <b><i><? printf ('%0.2f', $TempsCumul); ?> hres</i></b><br></font><br>
<br>
<font size="+1">Sélection courante = De: <b><i><? printf ($date_deb); ?></i></b> À: <b><i><? printf ($date_fin); ?></i></b><br></font><br>
<br>
<?
?>
===> END <===

Hope this will help...


Stéphane Lambert, Analyst/Programmer - BI Specialist
Info Quest (IQ), Data Warehouse System
McKesson Canada Corporation

EMail: stephane.lambert@mckesson.ca

 

[sleipnir214]

I don't see any date math anywhere in that.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[lstephane]

Sorry.... it's in french....

Please refer to:

$HreVal = $HreVal + ( ($row_tot["service_qtr_heure_fin"] - $row_tot["service_qtr_heure_deb"])



Stéphane Lambert, Analyst/Programmer - BI Specialist
Info Quest (IQ), Data Warehouse System
McKesson Canada Corporation

EMail: stephane.lambert@mckesson.ca

 

[sleipnir214]

I take it then that "service_qtr_heure_fin" and "service_qtr_heure_deb" are datetime values from MySQL?

If so, PHP does not perform date math on date strings. To find the difference between two dates, one generally converts both strings to integer seconds-counts, then performs the substraction.

Take a look at PHP's strtotime() function to convert a MySQL date string to seconds: online manual

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[lstephane]

OK.... thanks... I will try...


Stéphane Lambert, Analyst/Programmer - BI Specialist
Info Quest (IQ), Data Warehouse System
McKesson Canada Corporation

EMail: stephane.lambert@mckesson.ca

 

[lstephane]

Do you know how to convert it back as day, hours, minutes ?

Thanks !


Stéphane Lambert, Analyst/Programmer - BI Specialist
Info Quest (IQ), Data Warehouse System
McKesson Canada Corporation

EMail: stephane.lambert@mckesson.ca

 

[sleipnir214]

There's no PHP builtin function. You'll have to do some integer division to get the numbers you need.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!