Calculate time ago using sql

Question

Let's say i've the following table test and it has columns id, time, post and this is a sample of the data it has.

-----------------------------------
   id           time        post
-----------------------------------     
    1   2018-06-17 16:12:30 post1
    2   2018-06-17 16:13:09 post2
    3   2017-06-15 16:12:30 post7
----------------------------------

I want to print out using only SQL how many Days,hours,minutes and seconds ago since each post has been added to the database

Okay, here is my first try

SELECT
    id,
    time,
    NOW(),
    CONCAT (
    FLOOR(TIME_FORMAT(SEC_TO_TIME(NOW() - `time`), '%H') / 24),
    ' Days ',
    MOD (TIME_FORMAT(SEC_TO_TIME(NOW() - `time`), '%H'), 24),
    ' Hours ',
    TIME_FORMAT(SEC_TO_TIME(NOW() - `time`), '%i Minutes %s Seconds'),
    ' ago' 
    ) AS `ago` 
FROM
    `test`;

but it seems it does not giving correct calculations, For example the output of the sample above was

1   2018-06-17 16:12:30 2018-06-17 20:38:08 0 Days 11 Hours 49 Minutes 38 Seconds ago
2   2018-06-17 16:13:09 2018-06-17 20:38:08 0 Days 11 Hours 48 Minutes 19 Seconds ago
3   2017-06-15 16:12:30 2018-06-17 20:38:08 34 Days 22 Hours 59 Minutes 59 Seconds ago

as you can see, in id = 3 however the difference should be more not only 34 days as it print out

so what is wrong with the code ?! ~ thank you

Answer 1

You can try this query. Calculating the intervals by TIMESTAMPDIFF and TIMESTAMPADD function.

SELECT
    id,
    time,
    NOW(),
    CONCAT (
    TIMESTAMPDIFF(DAY,`time`, NOW()),' Days ',
    TIMESTAMPDIFF(HOUR, TIMESTAMPADD(DAY, TIMESTAMPDIFF(DAY, `time`, NOW()), `time`), NOW()),' Hours ',
    TIMESTAMPDIFF(MINUTE, TIMESTAMPADD(HOUR, TIMESTAMPDIFF(HOUR, `time`, NOW()), `time`), NOW()), ' Minutes ',
    TIMESTAMPDIFF(SECOND, TIMESTAMPADD(MINUTE, TIMESTAMPDIFF(MINUTE, `time`, NOW()), `time`), NOW()), ' Seconds '
    ' ago' ) AS `ago` 
FROM
    `test`;

sqlfiddle:http://sqlfiddle.com/#!9/5e8085/2

NOTE

For example, you want to get Interval on SECOND

let us step by step to know it:

1.TIMESTAMPDIFF(MINUTE, time, NOW()) get Interval MINUTE between time and NOW()

2.TIMESTAMPADD(MINUTE, TIMESTAMPDIFF(MINUTE, time, NOW()), time) append Interval MINUTE to time, let them only have Interval time on second.

3.Calculating intervals on second.

Answer 2

You may use TIMESTAMPDIFF with DATE_ADD for the missing parts( year and month ) of your query :

SELECT CONCAT (TIMESTAMPDIFF(YEAR, `time`, NOW()),' Year ',
           TIMESTAMPDIFF(MONTH, DATE_ADD(`time`, 
                        INTERVAL TIMESTAMPDIFF(YEAR, `time`, NOW()) YEAR), NOW()),
           ' Month ') as "Time(Year|Month)"
 FROM `test`;

SQL Fiddle Demo