Calculate Days difference from Datetime previous row

Question

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

What version of MySQL are you using? – Tim Biegeleisen Jan 17 '19 at 13:51 — Tim Biegeleisen, Jan 17 '19 at 13:51

score 3 · Accepted Answer · answered Jan 17 '19 at 13:53

3

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.

answered Jan 17 '19 at 13:53

Tim Biegeleisen

502,043
27
286
360

I'm curious, how would you have done it using session variables? – Cid Jan 17 '19 at 13:58
@Cid Have a look at [Simulate lag function in MySQL](https://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql). I find those queries to be painful to write, and I don't write them often, hence I offered the above solution (which the OP was already using for the most part). – Tim Biegeleisen Jan 17 '19 at 14:09

Calculate Days difference from Datetime previous row

1 Answers1