CODEWITHSUNDEEP
phpmysql
HbrownMedic
HbrownMedic

Reputation: 25

Convert MYSQL time from HH:MM:SS to HH:MM during PHP Select statement

I am using a PHP Select statement to pull data from MySQL. The eventTime field is stored as TIME in the schema as HH:MM:SS but I only want HH:MM. I have tried using TIME_FORMAT(eventTime, '%H:%i') in the select statement but it does not work. Any ideas? I have even tried to convert the variable in PHP but I am stumped. Please help me identify what I'm missing. 

$query="SELECT id,eventDate,eventTime, eventName FROM specialevents ORDER BY eventDate,eventTime ASC";

$result=$mysqliConnection->query($query,MYSQLI_STORE_RESULT);

while($row =$result->fetch_object())
  {


     $id=$row->id;
     $eventDate=$row->eventDate;
     $eventTime=$row->eventTime;
     $eventName=$row->eventName;
     $reportTime=$row->reportTime;

 echo '<tr>’; 
 echo '<td class="tdId"><input type="text" class="tbId" value="' . $id . '"/></td>’; 
 echo '<td class="tdEventDate"><input type="text" class="tbEventDate" value="' . $eventDate . '"/></td>’; 
 echo '<td class="tdEventTime"><input type="text" class="tbEventTime" value="' . $eventTime . '"/></td>’; 
 echo '<td class="tdEventName"><input type="text" class="tbEventName" value=" ' . $eventTime . '"/></td>’; 
 echo '<td class="tdReportTime"><input type="text" class="tbReportTime" value=" ' . $reportTime . '"/></td>';   

}

Upvotes: 2

Views: 3170

Answers (3)

Ahmed Soliman
Ahmed Soliman

Reputation: 1710

Assuming you have column of type time The Php approach as follows :

$new_time = date('H:i', strtotime($mysql_timedate)); // mysql_timedate is the row containg time form the db and outout is hour:mins

Mysql approach :

$sql = "SELECT DATE_FORMAT(column name here, '%H:%i') FROM table name"; // output hour:mins

Upvotes: 0

Kermit
Kermit

Reputation: 34055

You shouldn't be storing date and time separately if they refer to the same entity. However, you can use MySQL's TIME_FORMAT() function to format your time:

TIME_FORMAT(date,format)

Formats the date value according to the format string.

$query="SELECT id, eventDate, TIME_FORMAT(eventTime, '%H:%i') AS eventTime, eventName 
FROM specialevents 
ORDER BY eventDate, eventTime ASC";

You can use the specifiers from DATE_FORMAT to format as you wish.

Also, the ASC sort order is default and can be omitted from your query.

Upvotes: 4

Jono20201
Jono20201

Reputation: 3205

In PHP (not sure on MySQL) you can do the following:

$formatted_date = date('H:i:s', strtotime($mysql_timedate));

Upvotes: 0

Related Questions