Adding Minutes/Seconds

[DonP]

I have a MySQL database Varchar field that has minutes and seconds entered as text, ie: 3:22. I need to add the column for a total time but can't seem to figure out what function to use for converting this partial date-time to the Unix timestamp where it can be added properly. Also, it is really not necessary nor desired to convert the minutes to hours if there are more than 60 minutes in the total. In other words, a total of 72:30 is preferrable to 1:12:30, for example. I am not yet very knowledgable in PHP to create my own function. Can anyone help?

Don
contact@pc-homepage.com

http://www.pc-homepage.com/

Experienced in HTML, Perl, PHP, VBScript, PWS, IIS and Apache and MS-Access, MS-SQL, MySQL databases

 

[DRJ478]

Don,

You have several problems here. The first one is that your field in the table definition is a character field. You cannot add in there, so there is no way to just do it as a SQL query.
If you had 2 fields, namely minutes and seconds and they were numeric, you could just write one SQL statement to add all records up (with where further filters through where clauses etc.).

Since you don't, here's what I suggest:
1. retrieve all records using the standard MySQL functions (see faq434-3850)
2. In the loop that reads all the records
a) split into minutes and seconds
b) convert all to seconds and add
c) calcualte the total and convert to your desired format

$times = explode(':',$row['yourTimeField'];
$timeTotal += $times[0]*60; // adding the minutes
$timeTotal += $times[1];    // adding the seconds

# convert to desired format
$displayTime = intval($timeTotal/60).':'.($timeTotal%60);

The display time is the integer of the number of minutes. The remaining seconds are calculated using the modulus function.