mysqlrankingtimestampdiff

MySQL Calculate Difference between a date and non-consecutive dates

I have this table in mysql, (which at this point will show fake columns and data due to legal matters):

date       user_id   timestamp  
2018-04-27  181 2018-04-27 08:28:33
2018-04-28  181 2018-04-28 03:01:22
2018-05-03  181 2018-05-03 03:04:48
2018-05-04  181 2018-05-04 03:38:12
2018-05-07  181 2018-05-07 05:03:57
2018-05-08  181 2018-05-08 03:02:43
2018-05-09  181 2018-05-09 00:19:39
2018-05-10  181 2018-05-10 00:12:45
2018-05-08  183 2018-05-08 01:46:18
2018-05-09  183 2018-05-09 01:54:40
2018-05-01  184 2018-05-01 00:15:25
2018-05-03  184 2018-05-03 02:21:48
2018-05-08  184 2018-05-08 02:40:42
2018-05-09  184 2018-05-09 01:45:29

And i would need on a 4th column to calculate the difference between the first day and all of the others for the same user_id.

Therefore for the first user i would need the 4th column to show on the first row '0' and then 1 and then 7, this being the difference between 2018-04-27 and 2018-05-03 and so, resetting with 0 when the user_id changes.

I'm using MySQl version 5.6.34.

Could someone please help me?

Thanks, George

Solution

  • One option would be to join to a subquery which finds the first date for each user, and then compute the date diff for each row:

    SELECT
    t1.date,
    t1.user_id,
    t1.timestamp,
    DATEDIFF(t1.date, t2.first_date) AS diff
FROM yourTable t1
INNER JOIN
(
    SELECT user_id, MIN(date) AS first_date
    FROM yourTable
    GROUP BY user_id
) t2
    ON t1.user_id = t2.user_id
ORDER BY
    t1.user_id,
    t1.date;