在 PHP/MySQL 查询中格式化日期 [英] Formatting date in PHP/MySQL query

问题描述

我有一个使用 MySQL 和 PDO 的 PHP/JSON 脚本，现在数据运行良好.代码:

I have a PHP/JSON script which uses MySQL and PDO and the data works well now. Code:

 <?php
header('Content-type: application/json; charset=utf-8');
header("access-control-allow-origin: *");

$dsn = "mysql:host=localhost;dbname=myradio";
$username=test;
$password=test;
$pdo = new PDO($dsn, $username, $password);
$rows = array();
    $stmt = $pdo->prepare("SELECT *, DATE_FORMAT(start, '%d%M') FROM mystation");
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo '{"success":true,"error":"","data":{"schedule":';
echo json_encode($rows);
echo "]}}";
?>

我如何使用 DATE_FORMAT 使我的字段开始和结束(都存储为 DATETIME)显示如下:16/08/2013 00:00:00

How would I, using the DATE_FORMAT, get my fields start and end (both stored as DATETIME) to appear like this: 16/08/2013 00:00:00

等等?

它正在正确显示数据，但我不太确定如何使 DATE_FORMAT 正常工作.

It is displaying the data properly, but I am not quite sure how to get DATE_FORMAT working.

推荐答案

将正确的格式字符串传递给查询中的 DATE_FORMAT:

Pass the proper format string to DATE_FORMAT in your query:

SELECT 
    *, 
    DATE_FORMAT(start, '%d/%m/%Y %H:%i:%s') AS the_date 
FROM mystation

然后当您遍历行时，格式化日期将在 the_date 列中可用.

Then the formatted date will be available in a the_date column when you loop through your rows.

以下由 Marty McVry 建议的格式化字符串也有效:

The following formatting string ,suggested by Marty McVry below, also works:

DATE_FORMAT(start, '%d/%m/%Y %T')

参考:DATE_FORMAT onMySQL手工录入

这篇关于在 PHP/MySQL 查询中格式化日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！