Jump to content

MySQL TIME Echo Issue


ashleysmithd
 Share

Recommended Posts

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.

Edited by ashleysmithd
Link to comment
Share on other sites

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));

  • Like 1
Link to comment
Share on other sites

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.

post-95403-0-61607000-1334788314_thumb.jpg

Edited by ashleysmithd
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...