我在MySQL中有Comments
表,它看起来像这个
ID | COMMENT | DATE_ADDED |
---|---|---|
1 | 2022-04-06 04:41:03 | |
2 | 2022-04-08 21:38:19 | |
3 | 2022-04-08 00:55:21 | |
4 | 2022-04-06 00:35:14 | |
5 | 2022-04-05 16:26:21 | |
6 | 2022-04-08 14:52:27 | |
7 | 2022-04-05 09:12:02 | |
8 | 2022-04-08 10:04:41 | |
9 | 2022-04-06 21:59:42 | |
10 | 2022-04-06 19:11:11 |
当每个人指导您使用timestampdiff
函数和case
时,他们都是对的。没有内置函数可以执行您想要的操作。您必须利用这些函数编写自己的逻辑。您可以处理以下查询:
select a.id, a.date_added,
(case when diff_year > 0 then concat(diff_year, ' years')
when diff_month > 0 then concat(diff_month, ' months')
when diff_week > 0 then concat(diff_week, ' weeks')
when diff_day > 0 then concat(diff_day, ' days')
when diff_hour > 0 then concat(diff_hour, ' hours')
when diff_minute > 0 then concat(diff_minute, ' minutes')
end) as diff_wd
from
(select id, date_added,
timestampdiff(year, date_added, current_timestamp) as diff_year,
timestampdiff(month, date_added, current_timestamp) as diff_month,
timestampdiff(week, date_added, current_timestamp) as diff_week,
timestampdiff(day, date_added, current_timestamp) as diff_day,
timestampdiff(hour, date_added, current_timestamp) as diff_hour,
timestampdiff(minute, date_added, current_timestamp) as diff_minute
from comments) as a
上面的查询确实可以运行,但是的,看起来有点低效和愚蠢。至少它有效。希望有人能提出更好的问题。
编辑
上面的代码基本上是,在子查询a
上,我们计算每个单元的所有timestampdiff
函数。然后,这些值存储在它们自己的列中,分别命名为diff_year
表示年份,diff_month
表示月份,diff_week
表示周等。一旦我们有了每个单位的diff,外部查询就会选择适当的标签("年"、"月"、"周"等(。如果给定标签的列为非零,我们将使用concat
函数格式化输出。
如果你需要区分单数和复数,你可以添加更多的条件来检查";diff";值不止一个,例如
when diff_month > 1 then concat(diff_month, ' months')
when diff_month > 0 then concat(diff_month, ' month')
上述条件将产生,如果diff_month
为2,则输出为"0";2个月"然而,如果diff_month
是1,则它将是"1";1个月">
假设DATE_ADDED
列的类型为timestamp
:
select timestampdiff(<UNIT>, <later-timestamp>, <earlier-timestamp>) as diff;
UNIT
可以是YEAR
、MONTH
、DAY
、HOUR
或SECOND
,并指定需要差值的单位。
您可以在MySQL中使用TIMESTAMPDIFF((函数。
语法:TIMESTAMPDIFF(单位,日期时间_expr1、时间_expr2(;
SELECT
ID
,COMMENT
,DATE_ADDED
,TIMESTAMPDIFF(HOUR, DATE_ADDED,CURRENT_TIMESTAMP) AS DIFF_TIME
FROM COMMENT;
此处提供更多详细信息https://www.w3resource.com/mysql/date-and-time-functions/mysql-timestampdiff-function.php