CODEWITHSUNDEEP
mysqldatediff
Naeem Ali
Naeem Ali

Reputation: 332

Calculate Days difference from Datetime previous row

I have a log_file table that I created with Created_at as datetime column , I need to get days between the rows by created_at 

select id,DATE(created_at), datediff( created_at, prev_date) as diff
from (select t.*,
         (select DATE(t2.created_at)
          from log_file t2
          where t2.id = t.id and
                DATE(t2.created_at) <  DATE(t.created_at)
          order by  t2.created_at desc
          limit 1
         ) as prev_date
  from log_file t
 )  t

but its give me Null in diff

    id DATE(created_at) diff
     2 2019-01-16       NULL
     3 2019-01-19       NULL
     4 2019-01-21       NULL

Upvotes: 2

Views: 219

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

If you are using a version of MySQL earlier than 8+, and you also don't want to use session variables, then a correlated subquery is one way to go here.

SELECT
    t1.id,
    t1.created_at,
    DATEDIFF(t1.created_at,
             (SELECT t2.created_at FROM log_file t2
              WHERE t2.created_at < t1.created_at
              ORDER BY t2.created_at DESC LIMIT 1)) AS diff
FROM log_file t1;

You were on the right track, but the WHERE clause in your subquery has an incorrect added condition:

where t2.id = t.id

Remove it, and your current code might even work as is.

Upvotes: 3

Related Questions