如何用PHP将MySQL的timeDiff列更新为相对时间格式？

首先提醒：不要把相对时间（比如“2年前”）存入数据库，因为这类时间是动态变化的——今天显示“2年前”，明年就该显示“3年前”，存进去只会导致数据过时，完全没必要维护timeDiff这个冗余列，直接基于timePost字段实时计算即可。

方案一：PHP处理（推荐，灵活可控） #

直接从数据库取出发布时间，用PHP转换成相对时间，适配各种场景。

1. 修改查询语句，只取核心字段 #

// 假设已建立数据库连接$conn
$query = "SELECT name, timePost FROM story";
$result = mysqli_query($conn, $query);

2. 编写相对时间转换函数 #

function getRelativeTime($postTime) {
    $currentTimestamp = time();
    $postTimestamp = strtotime($postTime);
    $timeDiff = $currentTimestamp - $postTimestamp;

    // 按从大到小的时间单位定义
    $timeRules = [
        ['unit' => 'year',   'seconds' => 31536000],
        ['unit' => 'month',  'seconds' => 2592000],
        ['unit' => 'day',    'seconds' => 86400],
        ['unit' => 'hour',   'seconds' => 3600],
        ['unit' => 'minute', 'seconds' => 60],
        ['unit' => 'second', 'seconds' => 1]
    ];

    foreach ($timeRules as $rule) {
        $count = floor($timeDiff / $rule['seconds']);
        if ($count > 0) {
            return $count . ' ' . ($count > 1 ? $rule['unit'] . 's' : $rule['unit']) . ' ago';
        }
    }

    return 'just now';
}

3. 遍历结果输出 #

while ($row = mysqli_fetch_assoc($result)) {
    echo "作者：{$row['name']}，发布于：" . getRelativeTime($row['timePost']) . "<br>";
}

方案二：MySQL直接生成（适合简单场景） #

如果不想用PHP处理，也可以直接在SQL查询时生成相对时间字符串：

SELECT 
    name,
    timePost,
    CASE
        WHEN TIMESTAMPDIFF(YEAR, timePost, NOW()) > 0 THEN CONCAT(TIMESTAMPDIFF(YEAR, timePost, NOW()), ' years ago')
        WHEN TIMESTAMPDIFF(MONTH, timePost, NOW()) > 0 THEN CONCAT(TIMESTAMPDIFF(MONTH, timePost, NOW()), ' months ago')
        WHEN TIMESTAMPDIFF(DAY, timePost, NOW()) > 0 THEN CONCAT(TIMESTAMPDIFF(DAY, timePost, NOW()), ' days ago')
        WHEN TIMESTAMPDIFF(HOUR, timePost, NOW()) > 0 THEN CONCAT(TIMESTAMPDIFF(HOUR, timePost, NOW()), ' hours ago')
        WHEN TIMESTAMPDIFF(MINUTE, timePost, NOW()) > 0 THEN CONCAT(TIMESTAMPDIFF(MINUTE, timePost, NOW()), ' minutes ago')
        ELSE CONCAT(TIMESTAMPDIFF(SECOND, timePost, NOW()), ' seconds ago')
    END AS timeDiff
FROM story;

内容的提问来源于stack exchange，提问作者Meng Qing