Calculate the difference between dates

There is a Mysql table with a "data" column with the datetime type. You need to take the current date and time and calculate the difference with the table value and output the result in the format dd-hh-mm

SELECT SEC_TO_TIME(UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(users.data)) from users where UserId=999929921;

I tried it like this, everything works as long as the difference between the dates is not more than 1 day, then an error is displayed, since SEC_TO_TIME works in the interval of 24 hours, unfortunately. Actually, the question is, how to write a query correctly?

Author: Alexshev92, 2018-12-25
Source

2 answers

You can for example do something like this:

concat(lpad(floor((@h:=time_format(@tm:=timediff(now(), users.data), '%H'))/24), 2, '0'),
       '-',
       lpad(@h % 24, 2, '0'),
       '-',
       time_format(@tm, '%i'))

The question is whether it is worth doing such cumbersome constructions in SQL, if the client usually has much more time formatting tools.

Example on SQLFiddle.com

 1
Author: Mike, 2018-12-25 16:50:28

You can get the diff in seconds and divide and round down to build the desired date. Here is an example without formatting and only for days-hours. The rest, I think, is not difficult to finish, if the solution suits.

SELECT
    t.total_seconds,
    t.total_days,
    FLOOR((t.total_seconds - (t.total_days * 24 * 60 * 60)) / (60 * 60)) AS total_hours
FROM (
    SELECT
        t.total_seconds,
        FLOOR(t.total_seconds / (24 * 60 * 60)) AS total_days
    FROM (
        SELECT TIME_TO_SEC(
            TIMEDIFF("2018-12-25 18:40:00", "2018-12-23 15:20:30")
        ) AS total_seconds
    ) t
) t

Output:

total_seconds total_days total_hours
       184770          2           3
 2
Author: Lexx918, 2018-12-25 16:03:30