Time Difference - Duration - PHPBuilder Forums

Time Difference - Duration

Rodders

I cannot for the life of me work out how to do what seems like a simple thing - calculate the time difference between two dates/times.

I have a MySQL table with the following fields

START_DT (Date)
START_TIME (Time)
END_DTTM (Date & Time)

I know I could probably combine START_DT & START_TIME but for the moment I don't think that gives me a huge help when I can use 'concat()' in my SQL.

I'm almost 100% sure that my START_DT and END_DT will always be the same.

As an example, I have an appointment that starts at 08:30 and ends at 09:30 on the same day. So how can I find out that this is 1 hour using SQL or PHP ?

Sorry for the trouble !

Rich

devinemke

(i'm assuming you are using mySQL) store you start and end as a DATETIME type (YYYY-MM-DD HH:MM:SS) then you could simply do:

$result = mysql_query('SELECT start, end, UNIX_TIMESTAMP(start) AS start_timestamp, UNIX_TIMESTAMP(end) AS end_timestamp FROM table');
$row = mysql_fetch_assoc($result);
$diff = $row['end_timestamp'] - $row['start_timestamp'];

echo 'the difference between ' .  $row['start'] . ' and ' . $row['end'] . ' is ' . $diff . ' seconds.';

if you have mySQL 4.1.1 or higher you can also use the DATEDIFF() and TIMEDIFF() functions.