如何使用MySQL函数将日期转换为相对时间格式（如3天前）——针对Comments表的查询需求

当然可以！完全不用依赖PHP，直接用MySQL自带的函数就能实现你要的相对时间格式转换。

实现思路 #

核心是计算当前时间与DATE_ADDED字段的时间戳差值（秒数），然后通过CASE WHEN语句匹配你指定的每个区间，返回对应的文本描述。

完整SQL查询语句 #

SELECT
    ID,
    COMMENT,
    DATE_ADDED,
    CASE
        -- 差值≤59秒 → 显示Now
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) <= 59 THEN 'Now'
        -- 60-119秒 → 2 minutes ago
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) BETWEEN 60 AND 119 THEN '2 minutes ago'
        -- 120-179秒 → 3 minutes ago
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) BETWEEN 120 AND 179 THEN '3 minutes ago'
        -- 180-239秒 → 4 minutes ago
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) BETWEEN 180 AND 239 THEN '4 minutes ago'
        -- 240-299秒 → 5 minutes ago
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) BETWEEN 240 AND 299 THEN '5 minutes ago'
        -- 300-3599秒 → 1 hour ago
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) BETWEEN 300 AND 3599 THEN '1 hour ago'
        -- 3600-7199秒 → 7 hours ago
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) BETWEEN 3600 AND 7199 THEN '7 hours ago'
        -- 25200-28799秒 → 23 hours ago
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) BETWEEN 25200 AND 28799 THEN '23 hours ago'
        -- 82800-86399秒 → 1 day ago
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) BETWEEN 82800 AND 86399 THEN '1 day ago'
        -- 86400-172799秒 → 4 days ago
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) BETWEEN 86400 AND 172799 THEN '4 days ago'
        -- 345600-431999秒 → 13 days ago
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) BETWEEN 345600 AND 431999 THEN '13 days ago'
        -- 1123200-1209599秒 → 184 days ago
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) BETWEEN 1123200 AND 1209599 THEN '184 days ago'
        -- 15897600-15983999秒 → 381 days ago
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) BETWEEN 15897600 AND 15983999 THEN '381 days ago'
        -- 32918400-33004799秒 → 816 days ago
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) BETWEEN 32918400 AND 33004799 THEN '816 days ago'
        -- 70502400-70588799秒 → 6418 days ago
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) BETWEEN 70502400 AND 70588799 THEN '6418 days ago'
        -- 554515200-554601599秒 → 28118 days ago
        WHEN (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) BETWEEN 554515200 AND 554601599 THEN '28118 days ago'
        -- 2429395200秒及以上 → 计算天数并返回对应格式
        ELSE CONCAT(FLOOR((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(DATE_ADDED)) / 86400), ' days ago')
    END AS RELATIVE_TIME
FROM Comments;

关键细节说明 #

1. 时间戳差值计算：UNIX_TIMESTAMP(NOW())获取当前时间的秒级时间戳，UNIX_TIMESTAMP(DATE_ADDED)将你的DATE_ADDED字段（DATETIME类型）转换为秒级时间戳，两者相减得到时间差（单位：秒）。
2. 区间匹配：CASE WHEN按照你给定的区间顺序依次判断，确保每个差值都能匹配到对应的文本。注意顺序很重要，要从最小的区间开始判断，避免大区间覆盖小区间。
3. 默认处理：最后一个ELSE分支处理最大的时间区间，通过将秒数除以86400（一天的总秒数）并取整，拼接成X days ago的格式。

内容的提问来源于stack exchange，提问作者Taha Sami