adding time in a column.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
naveendk.55
Forum Newbie
Posts: 24
Joined: Tue Aug 16, 2011 10:13 am

adding time in a column.

Post 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; 

User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: adding time in a column.

Post 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().
naveendk.55
Forum Newbie
Posts: 24
Joined: Tue Aug 16, 2011 10:13 am

Re: adding time in a column.

Post 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;  

mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: adding time in a column.

Post 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.
Post Reply