RE: [PHP] convertion from string to time & substracting

Yeah, if he kept CHAR, then he'd have to do all of that. We were trying to convince him to switch to a TIME column, though, to make things easier. Which he did...which does... :) ---John Holmes... > -----Original Message----- > From: Erik Price [mailto:pricee@hhbrown.com] > Sent: Friday, June 07, 2002 3:27 PM > To: holmes072000@charter.net > Cc: 'juaid'; php-general@lists.php.net > Subject: Re: [PHP] 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 > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php