How to convert mysql time

Question

My time in mysql is in the form hh:mm:ss How can I convert that to 12 hour time and also remove the seconds, because they are all 00.

ex:

14:30:00

mysql

$finalresult = mysql_query("SELECT * FROM events WHERE clubID IN (" . implode(',', $clubIDs) . ") ORDER BY date ASC, TIME(startTime) ASC");

while($finalrow = mysql_fetch_assoc($finalresult)) {

$originaltime = $finalrow['startTime'];
$newtime = TIME_FORMAT($originaltime, '%h:%i %p');

}

Answer 1

Use TIME_FORMAT:

SELECT TIME_FORMAT('14:30:00', '%h:%i %p'); /* 02:30 PM */

---

Change your PHP to something like this:

$finalresult = mysql_query("SELECT Field1, Field2, TIME_FORMAT(DateField, '%h:%i %p') AS TheTime FROM events WHERE clubID IN (" . implode(',', $clubIDs) . ") ORDER BY date ASC, TIME(startTime) ASC");

This involves getting rid of * and specifying each field you want.

Answer 2

If it's a TIME type, you can use the TIME_FORMAT function.

To get the 12-hour time, without seconds:

TIME_FORMAT(myTime, '%h:%i')

A time of 14:24:38 will come out as 2:24.

If you need the AM/PM designation now that you've converted to a 12-hour clock, simply add the %p format code:

TIME_FORMAT(myTime, '%h:%i %p')

For this, a time of 14:24:38 will come out as 2:24 PM.