Question 243801 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql datediff with single column per user

I have this table in my sql:
1 user1 2023-07-10
2 user1 2023-06-25
3 user2 2023-06-21
4 user3 2023-06-27
5 user3 2023-07-11
6 user4 2023-07-14
7 user2 2023-07-16
8 user1 2023-07-17
9 user4 2023-07-15
10 user5 2023-03-06

I need to calculate the difference for every consecutive date but per user (something like this):
user1 2023-06-25 2023-07-10 15
user1 2023-06-10 2023-07-17 7
user2 2023-06-21 2023-07-16 25
user3 2023-06-27 2023-07-11 14

and so on. I already searched online but found only solutions without per user.

UPDATE:
I arrived at this query that is very good but the output have multiple times the same date as start which I don’t want:

SELECT 
    p1.id AS id,
    p1.name AS user,
    p1.date AS date1,
    p2.date AS date2,
    DATEDIFF(p2.date, p1.date) AS date_difference
FROM prova p1
JOIN prova p2 ON p1.name = p2.name AND p2.date > p1.date
ORDER BY p1.name, p1.date;

SQL Fiddle

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Found a solution:

    SELECT
    p1.id AS id,
    p1.name AS user,
    p1.date AS date1,
    p2.date AS date2,
    DATEDIFF(p2.date, p1.date) AS date_difference
FROM prova p1
JOIN prova p2 ON p1.name = p2.name AND p1.date < p2.date
LEFT JOIN prova p3 ON p1.name = p3.name AND p1.date < p3.date AND p3.date < p2.date
WHERE p3.id IS NULL
ORDER BY p1.name, p1.date

    SQL Fiddle


  2. 0

    use a querie like this

    SELECT `name`, `date`, DATEDIFF( 
    (SELECT `date` FROM prova sub 
        WHERE sub.`name` = main.`name` AND sub.`date` > main.`date` ORDER BY sub.`date` LIMIT 1)
        ,`date`) as nxttime 
  FROM prova AS main 
  order by `name`, `date`;

    sample

    http://sqlfiddle.com/#!9/aa0faf69/34

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search