Re: convertion from string to time & substracting

I thought that it was a CHAR column. I didn't know you could use UNIX_TIMESTAMP() on CHAR columns, so I was showing him how to turn his colon delimited strings into timestamps with mktime(). Erik On Friday, June 7, 2002, at 02:27 PM, John Holmes wrote:

Umm...How about using UNIX_TIMESTAMP() in your query, then you won't need any of that PHP code you just wrote... ---John Holmes...

-----Original Message----- From: Erik Price [mailto:pricee@hhbrown.com] Sent: Friday, June 07, 2002 2:22 PM To: juaid Cc: php-general@lists.php.net Subject: Re: [PHP] convertion from string to time & substracting On Friday, June 7, 2002, at 02:01 PM, juaid wrote:

sorry if maybe this is a bit dumb question, but I'm a begginer with php... I got a mysql database, where two of the fields of a table record

times

as CHAR(8) in the format hh:mm:ss I want to take this two times and get the difference between them in seconds, for example 12:01:30 - 12:00:00 = 90 I looked up at the doc in the php.net website and couldn't get on

how

to do this... should I use the strtotime function? anyone has donde something like this, or knows what functions could help me to do this?

First of all, if you're storing time then you're better off using the DATETIME column type. Even though it may take a bit more space than CHAR(8), unless you absolutely need the ultimate in table

optimization,

use DATETIME. It makes comparisons and manipulations like the one you are trying to do much easier. (Because you can convert them to UNIX_TIMESTAMP and then do your math on that, which is a piece of

cake.)

But to answer your question, here's what I would do: // $db contains database connection handle $sql = "SELECT row FROM table WHERE where_clause"; // attempt to pull data from DB if (!$result = mysql_query($sql, $db)) { die('Query failed.'); } // assuming only one row is returned $row = mysql_result($result, 0); $time_array = explode(':', $row); $hours = $time_array[0]; $minutes = $time_array[1]; $seconds = $time_array[2]; // convert to timestamp so we can easily do math $timestamp = mktime($hours, $minutes, $seconds, 0, 0, 0); Now all of your times are in timestamp form, and you can do what you need to do. Erik

---- Erik Price Web Developer Temp Media Lab, H.H. Brown pricee@hhbrown.com

-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php