dongzhukuai8177
2014-07-04 20:44
浏览 53
已采纳

在PHP Select语句中将MYSQL时间从HH:MM:SS转换为HH:MM

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

}

图片转代码服务由CSDN问答提供 功能建议

我正在使用PHP Select语句从MySQL中提取数据。 eventTime字段在模式中以TIME存储为HH:MM:SS,但我只想要HH:MM。 我尝试在select语句中使用TIME_FORMAT(eventTime,'%H:%i'),但它不起作用。 有任何想法吗? 我甚至试图在PHP中转换变量,但我很难过。 请帮我确定一下我缺少的东西。

  $ query =“SELECT id,eventDate,eventTime,eventName FROM specialevents ORDER BY eventDate,eventTime ASC”; 
 
 $ result = $ mysqliConnection-&gt; query(  $ query,MYSQLI_STORE_RESULT); 
 
而($ row = $ result-&gt; fetch_object())
 {
 
 
 $ id = $ row-&gt; id; 
 $ eventDate = $ row-  &gt; eventDate; 
 $ eventTime = $ row-&gt; eventTime; 
 $ eventName = $ row-&gt; eventName; 
 $ reportTime = $ row-&gt; reportTime; 
 
 echo'&lt; tr&gt;  “;  
 echo'&lt; td class =“tdId”&gt;&lt; input type =“text”class =“tbId”value =“'。$ id。'”/&gt;&lt; / td&gt;';  
 echo'&lt; td class =“tdEventDate”&gt;&lt; input type =“text”class =“tbEventDate”value =“'。$ eventDate。'”/&gt;&lt; / td&gt;';  
 echo'&lt; td class =“tdEventTime”&gt;&lt; input type =“text”class =“tbEventTime”value =“'。$ eventTime。'”/&gt;&lt; / td&gt;';  
 echo'&lt; td class =“tdEventName”&gt;&lt; input type =“text”class =“tbEventName”value =“'。$ eventTime。'”/&gt;&lt; / td&gt;';  
 echo'&lt; td class =“tdReportTime”&gt;&lt; input type =“text”class =“tbReportTime”value =“'。$ reportTime。'”/&gt;&lt; / td&gt;';  
 
} 
   
 
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • doumang20060820 2014-07-05 03:53
    已采纳

    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.

    打赏 评论
  • dongling5411 2014-07-04 20:51

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

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

    打赏 评论
  • dop83362 2018-06-06 12:02

    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
    
    打赏 评论

相关推荐 更多相似问题