如何使用MySQL函式將日期轉換為時間前（3天前）？

我Comments在 MySQL 中有表，它看起來像這樣

我想將DATE_ADDED列轉換為a time ago使用 獲取行時SELECT * FROM Comments，我可以使用PHP語言來完成，但是可以直接通過MySQL函式來??完成嗎？

在 PHP 語言中，我可以這樣做

獲取當前日期作為時間戳，并獲取DATE_ADDED列值作為時間戳，然后像這樣計算它Current Date - DATE_ADDED = Timestamp result，然后我制定條件。

我想要的格式型別

• 現在
• 分鐘）
• 小時）
• 天）

使用時間戳的示例

Show the DATE_ADDED column value like this (Now), If the timestamp was between 59 and less

Show the DATE_ADDED column value like this (1 minute ago), If the timestamp was between 60 and 119
Show the DATE_ADDED column value like this (2 minutes ago), If the timestamp was between 120 and 179
Show the DATE_ADDED column value like this (3 minutes ago), If the timestamp was between 180 and 239
Show the DATE_ADDED column value like this (4 minutes ago), If the timestamp was between 240 and 299
Show the DATE_ADDED column value like this (5 minutes ago), If the timestamp was between 300 and 119

Show the DATE_ADDED column value like this (1 hour ago), If the timestamp was between 3,600 and 7,199
Show the DATE_ADDED column value like this (7 hours ago), If the timestamp was between 25,200 and 28,799
Show the DATE_ADDED column value like this (23 hours ago), If the timestamp was between 82,800 and 86,399

Show the DATE_ADDED column value like this (1 day ago), If the timestamp was between 86,400 and 172,799
Show the DATE_ADDED column value like this (4 days ago), If the timestamp was between 345,600 and 431,999
Show the DATE_ADDED column value like this (13 days ago), If the timestamp was between 1,123,200 and 1,209,599
Show the DATE_ADDED column value like this (184 days ago), If the timestamp was between 15,897,600 and 15,983,999
Show the DATE_ADDED column value like this (381 days ago), If the timestamp was between 32,918,400 and 33,004,799
Show the DATE_ADDED column value like this (816 days ago), If the timestamp was between 70,502,400 and 70,588,799
Show the DATE_ADDED column value like this (6418 days ago), If the timestamp was between 554,515,200 and 554,601,599
Show the DATE_ADDED column value like this (28118 days ago), If the timestamp was between 2,429,395,200 and 2,429,481,599

uj5u.com熱心網友回復：

當他們指導您發揮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周等。一旦我們有了每個單位單位的差異，外部查詢就會選擇正確的標簽（“年”、“月”、“周” ， ETC）。如果給定標簽的列不為零，我們將使用concat函式格式化輸出。

如果您需要區分單數和復數，您可以添加更多條件檢查“diff”值是否大于一，例如

  when diff_month > 1 then concat(diff_month, ' months')
  when diff_month > 0 then concat(diff_month, ' month')

上面的條件將導致，如果diff_month為 2，則輸出將為“2 個月”。但是，如果diff_month為 1，則為“1 個月”。

uj5u.com熱心網友回復：

假設您的DATE_ADDED列的型別是timestamp：

select timestampdiff(<UNIT>, <later-timestamp>, <earlier-timestamp>) as diff;

UNIT可以是YEAR, MONTH, DAY,HOUR或SECOND并指定您想要區別的單位。

uj5u.com熱心網友回復：

您可以在 MySQL 中使用 TIMESTAMPDIFF() 函式。

語法： TIMESTAMPDIFF( unit , datetime_expr1 , datetime_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

標籤：php mysql sql 数据库

上一篇：獲取當前周/月日期

下一篇：從型別為帶時區的時間戳的日期時間值中減去5小時