MySQL TIME Echo Issue

[ashleysmithd]

Hi, I'm new to MySQL and PHP so please excuse my ignorance, but I'm having difficulty echoing a MySQL query that consists of adding and subtracting information in the TIME format. My code is as follows:

$result = mysql_query("SELECT Title, StartTimeHMS, StartTimeHMS + AssetOutHMS - AssetInHMS FROM scheduleINNER JOIN assetON asset.Asset_ID = schedule.Asset_IDWHERE StartTimeHMS < CURTIME()AND (StartTimeHMS + AssetOutHMS - AssetInHMS) > CURTIME()")or die(mysql_error()); $row = mysql_fetch_array( $result );echo $row['Title'];echo "<font color=#FFFFFF size=3 face=Arial><br><i>".$row['StartTimeHMS']." - ".$row['StartTimeHMS + AssetOutHMS - AssetInHMS'];

The problem occurs when echoing the calculation of the three pieces of TIME data,

$row['StartTimeHMS + AssetOutHMS - AssetInHMS'];

, where the echo displays the TIME data in the form 'HHMMSS', as opposed to 'HH:MM:SS' (with colons.) I've tried using different datatypes, including TIMESTAMP, but to no avail. The calculation works fine, it's just not displaying the data how I want it to. It only happens when I perform a calculation, when I echo the data otherwise it displays in the correct form. I'm sure it's something simple, but I've had a Google around but haven't found anything that works. Any help would be appreciated. Thank you.

[ashleysmithd]

I've attached an example JPG to this post of the problem I'm having.

[justsomeguy]

It may make it easier to use an alias for that field in the result: $result = mysql_query("SELECT Title, StartTimeHMS, StartTimeHMS + AssetOutHMS - AssetInHMS AS EndTime FROM schedule... Then you can refer to $row['EndTime'] instead of using the entire calculation. To add the colons you can use str_split and implode: echo implode(':', str_split($row['EndTime'], 2));

[ashleysmithd]

Brilliant, works a treat. Thanks very much!

[ashleysmithd]

One more quick question if I may, there appears to be some issues when sticking to base 60 information, as when I perform calculations it appears to be converting to a base 10 system, as per the attached diagram. In the attached screenshot, the calculation, 'StartTimeHMS + AssetOutHMS - AssetInHMS', is calculating the data '23:10:00 + 00:40:00 - 00:00:05'. However gives the answer '23:49:95' as opposed to the expected '23:49:55'. Any suggestions as to what the problem might be? Thanks again.

[justsomeguy]

It's treating them as numbers instead of time stamps. You can use addtime and subtime to add and subtract time ranges in MySQL: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_addtime

[ashleysmithd]

Thanks a lot, I used

SUBTIME(ADDTIME(StartTimeHMS, AssetOutHMS), AssetInHMS) AS EndTime

and it worked a treat. Didn't need the implode function in the end, when using the SUBTIME and ADDTIME functions it displayed correctly. Many thanks.