How can I convert date to time ago (3 days ago) using MySQL functions?-CodePudding

I have the Comments table in MySQL and it looks like this

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

I want to convert the DATE_ADDED column to a time ago when I get the rows using SELECT * FROM Comments, I can do it using PHP language, But is it possible to do it directly by MySQL functions?

In PHP language I can do it like this

Get the current date as timestamp and get the DATE_ADDED column value as timestamp and after that calculate it like this, Current Date - DATE_ADDED = Timestamp result, After that I make conditions.

Types of formats I want

Now
Minute(s)
Hour(s)
Day(s)

Examples using timestamp

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

CodePudding user response：

Everyone is right when they directed you to timestampdiff function and case. There is no built-in function to do what you want. You have to write your own logic utilizing those functions. You can work a query like:

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

The query above do run, but yes, kinda look inefficient and dumb. At least it works. Hopefully someone come with better query.

EDIT

What happens with the code above is basically, on the sub-query a we calculate all of the timestampdiff function for each unit. The values are then stored in their own columns named diff_year for years, diff_month for months, diff_week for weeks, etc. Once we have the diffs for each unit units, the outer query selects the proper labels ("years", "months", "weeks", etc). If the column for the given label is non-zero, we will then format the output using concat function.

If you need to differentiate between singular and plurals, you could add more conditions checking if the "diff" value is more than one, e.g.

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

The conditional above will result, if the diff_month is 2, the output will be "2 months." However, if the diff_month is 1, it will be "1 month."

CodePudding user response：

Assuming the type of your DATE_ADDED column is timestamp:

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

UNIT can be YEAR, MONTH, DAY, HOUR or SECOND and specifies the unit you want the difference in.

CodePudding user response：

You can use the TIMESTAMPDIFF() function in MySQL.

Syntax: TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2);

SELECT 
  ID
  ,COMMENT
  ,DATE_ADDED 
  ,TIMESTAMPDIFF(HOUR, DATE_ADDED,CURRENT_TIMESTAMP) AS DIFF_TIME
FROM COMMENT;

More details here https://www.w3resource.com/mysql/date-and-time-functions/mysql-timestampdiff-function.php