Page 1 of 1
adding time in a column.
Posted: Mon Nov 28, 2011 8:40 am
by naveendk.55
I have a column call time_utilization in table. This contains the values in HH:MM:SS and its datatype is varchar. If it is required to change the datatype to time, I'll willing to do so.
I am trying to add the total time in this column. I tried the below thing and it didn't work. Any help is appreciated.
Code: Select all
$qry = "SELECT SEC_TO_TIME(SUM(SECOND(time_utilization))) FROM tracker";
$result=mysql_query($qry) or die (mysql_error());
$row = mysql_fetch_array($result);
echo $row;
Re: adding time in a column.
Posted: Mon Nov 28, 2011 10:08 am
by McInfo
The SECOND() function returns the seconds part, not total seconds. For example, SECOND('01:26:33') returns 33, not 5193. To convert a time to total seconds, use TIME_TO_SEC().
Re: adding time in a column.
Posted: Mon Nov 28, 2011 11:09 am
by naveendk.55
I tried earlier TIME_to_sec and the output is ARRAY and not the total sum. Below is the exact code to get the sum in php.
time_utilization is the column name in table.
Code: Select all
$qry = "SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_utilization))) FROM tracker";
$result=mysql_query($qry) or die (mysql_error());
$row = mysql_fetch_array($result);
echo $row;
Re: adding time in a column.
Posted: Mon Nov 28, 2011 12:20 pm
by mikosiko
naveendk.55 wrote:...and the output is ARRAY and not the total sum...
well...
mysql_fetch)array() return an ARRAY
Description
array mysql_fetch_array ( resource $result [, int $result_type = MYSQL_BOTH ] )
Returns an array that corresponds to the fetched row and moves the internal data pointer ahead.
if you want to see the result you must use $row[0] or similar...
change your "echo $row" for var_dump($row) to see the values.